A way to auto insert at top of each column with each entry?

[Guest]

I have a lot of data I'm entering into a sheet like this:

A B C D
1 2 3
2 4
2
3
1
3

Simple. But some columns end up with little or nothing in them, some get a
hundred+ entries. When I enter something in "A", then need to enter the next
value into "B", I have to move to the right column, scroll up or jump to
bottom, then enter. Back to A? Same thing. Lots of scrolling up and down.

Is there some way to set up a row at the top that's the "entry" row so each
entry is in the same place vertically and the numbers pop down, as if I hit
insert before each entry? Then I would only have to move left and right and
the values would cascade down from that row. Kind of like doing a split to
keep the column titles at the top, this would keep the next empty cell of
each column at the top.

Does that make any sense? Any ideas?

 

[PCLIVE]

If you need to scroll up to where the data is in the row that you're in, you
can press End + the Up Arrow, or End + the Down Arrow to scroll down to the
bottom of data in a column.

HTH,
Paul

 

[Guest]

oh no doubt, that works. But doing that hundreds of times isn't much fun!

 

[Ken Johnson]

Hi Channing,

Does this Worksheet_Change event procedure do what you want?...

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A11")) Is Nothing Then
Application.EnableEvents = False
Dim rngCell As Range
For Each rngCell In Target
Cells(IIf(rngCell.End(xlDown).Row = _
Columns(rngCell.Column).Rows.Count, _
2, rngCell.End(xlDown).Row + 1), rngCell.Column).Value _
= rngCell.Value
rngCell.ClearContents
Next
Application.EnableEvents = True
End If
End Sub

If you type or paste values into A11 they are cut and pasted into the
next available cell at the bottom of the same column.

Copy and paste the code into the worksheet's code module.

Ken Johnson

 

[Bill Kuunders]

Would have to be done with a macro.

Assign the macro to a button on the sheet and press the button each time you
have a line of data in row 2

Assuming row 1 has headers
row 2 is your data entry row
row 3 is the first row of the data table
you do have to have values in row 3
this macro will only check up tp 24 columns

I'm sure others will be able to give you a better written version

But here goes.....


Sub Macro4()

Dim cc As Long
'columns count

Range("A2").Select
cc = 1
For Each Column In Columns
Cells(2, cc).Select
Selection.Copy
If Selection.Value = "" Then
GoTo 1
End If
Selection.End(xlDown).Offset(1, 0).PasteSpecial 'row 3 line has to
have a value.

'to be able to find the end of column
Application.CutCopyMode = False
1 cc = cc + 1
If cc = 24 Then 'you can change this to suit
GoTo 2
End If

Next Column
2 Rows(2).Clear
Range("A2").Select
End Sub

Greetings from New Zealand
Bill Kuunders