Excel Macro - "Do until last row is reached...
I would like to create a generic macro that identifies the last row in a column, and saves that number to repeat another recorded macro until that number is reached. For instance, if there were 709 rows identified by something like
Range("1000000").End(x1Up)
Range("1000000").End(x1Up)
ASKER
Thanks, I apparently didn't do a good job in defining the right code, but I am open to how this is done. In your example, if "lr" is equal to 709, how do I save that number and have another macro run 709 times?
You can define the variable lr as a global variable like
public dim lr as long
This should go at the top of the module before any other sub
public dim lr as long
This should go at the top of the module before any other sub
Change the column to whatever column you are interested and this will give you 709
Dim r As Range
Set r = Range("A1").End(xlDown).Of
MsgBox r.Row
Dim r As Range
Set r = Range("A1").End(xlDown).Of
MsgBox r.Row
And using ssaqibh's lr
lr = r.Row
lr = r.Row
Alternatively you can also store the value in a predefined cell of a worksheet and then directly pick it from there.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Okay a lot of thoughts here. Maybe the question is now, what changes do I need to make to this code to ensure "MyMacroPerformedOnEachRow
Sub IdentifyLastRow()
Dim x As Integer
Range("A1000000").End(xlUp
x = ActiveCell.Row
Do Until ActiveCell = x
"MyMacroPerformedOnEachRow
Loop
End Sub
Sub IdentifyLastRow()
Dim x As Integer
Range("A1000000").End(xlUp
x = ActiveCell.Row
Do Until ActiveCell = x
"MyMacroPerformedOnEachRow
Loop
End Sub
You can try
for each cel in Range("A1:A" & rows.count).end(xlup)
"perform your code here using cel as a range which takes one cell at a time from A1:An"
next cel
for each cel in Range("A1:A" & rows.count).end(xlup)
"perform your code here using cel as a range which takes one cell at a time from A1:An"
next cel
This sub will call your macro for each row from the active row to the last row.
Sub IdentifyLastRow()
Dim x As Long
For x = ActiveCell.Row To Cells.Find("*", [A1], xlValues, xlWhole, xlByRows, xlPrevious).Row
Call MyMacroPerformedOnEachRow
Next x
End Sub
Sorry change the first line of code in my last comment to
for each cel in Range("A1:A" & range("A" & rows.count).end(xlup).row)
for each cel in Range("A1:A" & range("A" & rows.count).end(xlup).row)
ASKER
Hi Guys - Thanks for the help. Both codes work, though ssaqibh code does not stop when the last row is reached. It appears to go through all million + rows in the sheet. Kyle's, on the other hand, stops when the last row is reached.
ASKER
Thank you for helping me on my question, Kyle. I hope to continue to better understand VBA so that I can do more solutions on my own. - Tom
You're welcome. Glad to help. If you need further explanation on anything we come up with just ask. Most of us are always happy to provide additional detail.
Kyle
Kyle
x1up should be xlup
lr = Range("A1000000").End(xlUp