Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Ctrl+Shift+Down in a macro - doesn't re-run
I'm using a macro to basically add new columns to data pulled from
Access. I pulled 4 extra columns that I can re-write to be the same length as the data I actually need. So in my macro, I recorded Ctrl+Shift+Down to paste the data the length of the column, but this function does not get translated when I pull data that is longer than the original data I wrote the macro on. Is there a way to re-write this so that it goes to the end of the column each time it is executed, no matter how long the column is? Here is the code I have: Range("P2").Select Selection.Copy Range("P3").Select Range(Selection, Selection.End(xlDown)).Select ActiveSheet.Paste ActiveWindow.SmallScroll Down:=-3 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Ctrl+Shift+Down in a macro - doesn't re-run
The code you have should work just fine. However there is a shorter way to do
it. Range("P2").Copy Range("P3",Range("P3").End(xlDown)).PasteSpecial xlPasteAll The Ctrl+Down thing is basically the "End(xlDown)" part. -- Charles Chickering "A good example is twice the value of good advice." "Astello" wrote: I'm using a macro to basically add new columns to data pulled from Access. I pulled 4 extra columns that I can re-write to be the same length as the data I actually need. So in my macro, I recorded Ctrl+Shift+Down to paste the data the length of the column, but this function does not get translated when I pull data that is longer than the original data I wrote the macro on. Is there a way to re-write this so that it goes to the end of the column each time it is executed, no matter how long the column is? Here is the code I have: Range("P2").Select Selection.Copy Range("P3").Select Range(Selection, Selection.End(xlDown)).Select ActiveSheet.Paste ActiveWindow.SmallScroll Down:=-3 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Ctrl+Shift+Down in a macro - doesn't re-run
I just realized that there were some empty cells, so the
ctrl+shift+down only selected up to the empty cell and didn't paste the cells below it. i'll have to write a function to get rid of rows with empty cells, or fill empty cells with a placeholder. Charles Chickering wrote: The code you have should work just fine. However there is a shorter way to do it. Range("P2").Copy Range("P3",Range("P3").End(xlDown)).PasteSpecial xlPasteAll The Ctrl+Down thing is basically the "End(xlDown)" part. -- Charles Chickering "A good example is twice the value of good advice." "Astello" wrote: I'm using a macro to basically add new columns to data pulled from Access. I pulled 4 extra columns that I can re-write to be the same length as the data I actually need. So in my macro, I recorded Ctrl+Shift+Down to paste the data the length of the column, but this function does not get translated when I pull data that is longer than the original data I wrote the macro on. Is there a way to re-write this so that it goes to the end of the column each time it is executed, no matter how long the column is? Here is the code I have: Range("P2").Select Selection.Copy Range("P3").Select Range(Selection, Selection.End(xlDown)).Select ActiveSheet.Paste ActiveWindow.SmallScroll Down:=-3 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Ctrl+Shift+Down in a macro - doesn't re-run
Or better yet work from the bottom up...
Range("P2").Copy Range("P3",Range("P" & Rows.Count).End(xlUp)).PasteSpecial xlPasteAll -- Charles Chickering "A good example is twice the value of good advice." "Astello" wrote: I just realized that there were some empty cells, so the ctrl+shift+down only selected up to the empty cell and didn't paste the cells below it. i'll have to write a function to get rid of rows with empty cells, or fill empty cells with a placeholder. Charles Chickering wrote: The code you have should work just fine. However there is a shorter way to do it. Range("P2").Copy Range("P3",Range("P3").End(xlDown)).PasteSpecial xlPasteAll The Ctrl+Down thing is basically the "End(xlDown)" part. -- Charles Chickering "A good example is twice the value of good advice." "Astello" wrote: I'm using a macro to basically add new columns to data pulled from Access. I pulled 4 extra columns that I can re-write to be the same length as the data I actually need. So in my macro, I recorded Ctrl+Shift+Down to paste the data the length of the column, but this function does not get translated when I pull data that is longer than the original data I wrote the macro on. Is there a way to re-write this so that it goes to the end of the column each time it is executed, no matter how long the column is? Here is the code I have: Range("P2").Select Selection.Copy Range("P3").Select Range(Selection, Selection.End(xlDown)).Select ActiveSheet.Paste ActiveWindow.SmallScroll Down:=-3 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
F9, Shift+F9, Ctrl+Alt+F9 etc | Excel Discussion (Misc queries) | |||
Ctrl Shift | Macro Stops | Creating Short Cut Keys | Excel Programming | |||
Can you perform ctrl+shift+; in a macro to display time? | Excel Discussion (Misc queries) | |||
CTRL+SHIFT+END | Excel Programming |