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).Select where ActiveCell.Row = 709, how could I save the 709 and then perform another macro I embed within this code until 709 is reached?  Does this make sense?
LVL 7
tomfarrarAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saqib Husain, SyedEngineerCommented:
You need to provide a column letter as well
x1up should be xlup

lr = Range("A1000000").End(xlUp).row
tomfarrarAuthor Commented:
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?
Saqib Husain, SyedEngineerCommented:
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
OWASP: Forgery and Phishing

Learn the techniques to avoid forgery and phishing attacks and the types of attacks an application or network may face.

Martin LissOlder than dirtCommented:
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).Offset(0, 0)
MsgBox r.Row
Martin LissOlder than dirtCommented:
And using ssaqibh's lr

lr = r.Row
Saqib Husain, SyedEngineerCommented:
Alternatively you can also store the value in a predefined cell of a worksheet and then directly pick it from there.
kgerbChief EngineerCommented:
If you don't know what column will contain the last row you can use the following.  It will return the last row of data regardless of which column it is in.
Sub FindLastRow()
Dim lRow As Long
lRow = Cells.Find("*", [A1], xlValues, xlWhole, xlByRows, xlPrevious).Row
End Sub

Open in new window

Kyle

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
tomfarrarAuthor Commented:
Okay a lot of thoughts here.  Maybe the question is now, what changes do I need to make to this code to ensure "MyMacroPerformedOnEachRow" is performed as many times as there is rows...  I know the code doesn't work now.  Thanks.

Sub IdentifyLastRow()

Dim x As Integer

Range("A1000000").End(xlUp).Select
x = ActiveCell.Row
Do Until ActiveCell = x

"MyMacroPerformedOnEachRow"

Loop

End Sub
Saqib Husain, SyedEngineerCommented:
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
kgerbChief EngineerCommented:
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

Open in new window

Kyle
Saqib Husain, SyedEngineerCommented:
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)
tomfarrarAuthor Commented:
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.
tomfarrarAuthor Commented:
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
kgerbChief EngineerCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.