Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
A way to auto insert at top of each column with each entry?
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
A way to auto insert at top of each column with each entry?
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 "Channing" wrote in message ... 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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
A way to auto insert at top of each column with each entry?
oh no doubt, that works. But doing that hundreds of times isn't much fun!
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
A way to auto insert at top of each column with each entry?
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("A1:D1")) 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 A1:D1 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
A way to auto insert at top of each column with each entry?
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 "Channing" wrote in message ... 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
macro | Excel Discussion (Misc queries) | |||
insert date | Excel Worksheet Functions | |||
creating a bar graph | Excel Discussion (Misc queries) | |||
match and count words | Excel Worksheet Functions | |||
Lookup then Match and insert value from next column | Excel Worksheet Functions |