Solved

Excel Macro - "Do until last row is reached...

Posted on 2012-03-29
14
387 Views
Last Modified: 2013-01-12
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?
0
Comment
Question by:tomfarrar
  • 5
  • 4
  • 3
  • +1
14 Comments
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 37783608
You need to provide a column letter as well
x1up should be xlup

lr = Range("A1000000").End(xlUp).row
0
 
LVL 7

Author Comment

by:tomfarrar
ID: 37783625
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?
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 37783639
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
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 37783693
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
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 37783698
And using ssaqibh's lr

lr = r.Row
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 37783718
Alternatively you can also store the value in a predefined cell of a worksheet and then directly pick it from there.
0
 
LVL 12

Accepted Solution

by:
kgerb earned 500 total points
ID: 37783848
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
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 7

Author Comment

by:tomfarrar
ID: 37784141
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
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 37784188
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
0
 
LVL 12

Expert Comment

by:kgerb
ID: 37784226
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
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 37784240
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)
0
 
LVL 7

Author Comment

by:tomfarrar
ID: 37784785
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.
0
 
LVL 7

Author Closing Comment

by:tomfarrar
ID: 37789474
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
0
 
LVL 12

Expert Comment

by:kgerb
ID: 37789525
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
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now