Expanding ranges in macros
 

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.

Expanding ranges in macros
 

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.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Jeff" wrote in message
...
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.

Expanding ranges in macros
 

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" wrote:

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.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Jeff" wrote in message
...
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.

Expanding ranges in macros
 

When all else fails, RTFI
As always, post your code for comments.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Jeff" wrote in message
...
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" wrote:

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.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Jeff" wrote in message
...
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.

Expanding ranges in macros
 

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...
--
HTH...

Jim Thomlinson


"Jeff" wrote:

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" wrote:

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.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Jeff" wrote in message
...
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.