Expanding ranges in macros

[Jeff]

I have a block of data A1..G50. I'm using a macro to add additional rows one
at a time. How do I set it up to find row 51 for the first add? Using macro
recorder, I started at cell A1 and used END DOWN and DOWN once more to get to
A51. This works the first time, but when I run the macro again, it stops at
A51 (should be A52 the second time). Thanks.

 

[Don Guillett]

lastrow=cells(rows.count,1).end(xlup).row+1

As always, post your code for comments. There are probably other things that
could stand a look.

 

[Jeff]

Don - Thanks, but I don't understand how to read your response. Is this
something I can do in the process of recording the macro? How do I use this?

 

[Don Guillett]

When all else fails, RTFI

As always, post your code for comments.

 

[Jim Thomlinson]

There is no effective way to record what you are doing in a macro. You need
to write it from scratch. To that end what Don's code is doing is it is
determining the last row number which you can use in your code. Perhaps this
will be more to your liking...

cells(rows.count, "A").end(xlUp).offset(1,0).Select

Place this single line of code where you want to select the first blank cell
in column A. What it does is it looks up from the bottom of column A of your
spreadsheet finding the first populated cell. It offsets down 1 row and
selects that cell...