VBA And Finding The First Empty Row

[The Cardinal]

Hi guys,
Looking for more help! This one should be quite simple though.

The system in use relies on the user copying and pasting a section from
one tab to another. This is 8 columns wide, and 7 rows deep. In the past
they've manually entered dates etc etc but I've used formulae to make
this more efficient. The problem is, when they copy and paste now, the
formulae update and are wrong. So I showed them the Paste Special
Feature, which they loved, but then promptly forgot how to use. Anyway,
I then decided to use a Macro attached to a button just to do it
automatically and be done with it.

So, I've gotten this far:

Sub CopyandPasteNewQuarter()


Range("A9:M29").Select
Selection.Copy
Sheets("Stream1").Select
Range("A258").Select
Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
End Sub

Now I did this just to check the commands would work (they do) and the
selection of Cell A258 in stream 1 was done manually as I knew that
that was the blank cell I needed. But obviously next quarter it won't
be that cell, but A266.
So what commands would I need to introduce to instruct the macro to
find the first blank row in a range, and then insert the block of cells
to be copied in there?

Cheers in advance!!!

 

[Andy]

"The Cardinal" <[email protected]>
wrote in message

Try using the .End(xldown/xlup/xlright/xlleft) command it is the program
equialtent of using ctrl & right/left/up/down arrow to navigate.
Worksheets("Main").Range("A1").End(xlDown) will return the last used cell in
column A assuming that the data is a complete column.
If you want the last used cell and the column may contain blanks then try
Worksheets("Main").Range("A65536").End(xlup) . This will start at the last
row and work upwards to the first occupied cell.

To find the first blank cell then just use the appropriate offset.

Andy

 

[The Cardinal]

Nice! Thanks very much...