simple macro command (shift-end-down_arrow)

Posted by Allan on March 16, 2001 12:40 PM

Hello,

I'm just trying to do something simple that will save
me a lot of time. I want to copy the contents of a
cell to a group of blank cells immediately below the
cell I am copying. I have tried the macro recorder
but the resulting macro has the range of cells I
am copying to always fixed (the same number of rows).
The problem is that the number of blank rows that I
want to copy to varies time to time(5 one time, 10 another time). I used the shift-end
down arrow keystroke to highlight the range to copy to
(which selects rows until the first non-blank cell is
encountered)but the recorder doesn't record it that
way; it records the exact number of rows. So when I
replay the macro, if the number of rows to copy to is
not the same as that in the example I used to record
the macro, it screws up my work sheet. I sure this
is a very easy thing to do... but Excel's help database
is not that helpful on macro commands.

Posted by Dave Hawley on March 16, 2001 2:07 PM

Hi Allan

Here are 2 ways, the first in a bit more generic as it uses your selected cell as it's starting point.

Sub TryThis()
Range(Selection, Selection.End(xlDown)).Copy _
Destination:=Selection.End(xlDown).Offset(1, 0)
End Sub

Sub OrThis()
Range("A1", [A1].End(xlDown)).Copy _
Destination:=[A1].End(xlDown).Offset(1, 0)
End Sub

Dave

OzGrid Business Applications