Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
auto-expanding chart- dynamic ranges | Excel Worksheet Functions | |||
using named ranges in macros | Excel Discussion (Misc queries) | |||
using named ranges in macros | Excel Discussion (Misc queries) | |||
Using Query and expanding named ranges | Excel Discussion (Misc queries) | |||
Named ranges in macros? | Excel Discussion (Misc queries) |