Using VBA to count the number of rows used in a worksheet

Amateurhr

Active Member
Joined
Dec 26, 2011
Messages
343
Let's say that I want to count the number of rows on a particular worksheet (Sheet1)

How would I do this in VBA? Here is the exact formula that works for me in Excel (note you have to turn this into an array to work):

Code:
=MAX(ROW(Sheet1)*(Sheet1<>""))

Thank you in advance!
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Activesheet.UsedRange.Rows.Count will return the number of rows used on the active sheet. They may not necessarily all of data.. it actually returns the row number for the last used row.
 
Upvote 0
Yep, I'm looking for the last used row number, even if it doesn't have real data... however, I'm trying to avoid declaring ActiveSheet in general to make things faster.

Any solutions that can reference a sheet without making me change to it first?
 
Upvote 0
I noticed this old post: http://www.mrexcel.com/td0058.html

At the bottom it cites:

Code:
LastRow = Cells.Find("*",SearchOrder:=xlByRows,SearchDirection:=xlPrevious).Row

or

LastRow = ActiveSheet.UsedRange.Rows.Count

"This methods can be used on any sheet, not just the active sheet."

But I don't see how you refer to the sheet name (let's say Sheet1)
 
Upvote 0
For the last used row on Sheet1 containing either constant data or a formula displaying data (that is, formulas displaying the empty string are ignored)...

Code:
LastUsedRow = Worksheets("Sheet1").Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row
For the last used row on Sheet1 even if that row is populated with a formula displaying the empty string...

Code:
LastUsedRow = Worksheets("Sheet1").Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlFormulas).Row
 
Upvote 0
You have to give it a sheet object of some sort - tell it what sheet you want to know the last used row on. Either Activesheet or the sheet name Worksheets("sheetname")
 
Upvote 0
Try Worksheets("Sheet1").UsedRange.Rows.Count

I noticed this old post: Excel Find the last row of data in a range

At the bottom it cites:

Code:
LastRow = Cells.Find("*",SearchOrder:=xlByRows,SearchDirection:=xlPrevious).Row

or

LastRow = ActiveSheet.UsedRange.Rows.Count

"This methods can be used on any sheet, not just the active sheet."

But I don't see how you refer to the sheet name (let's say Sheet1)
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top