Results 1 to 7 of 7

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

This is a discussion on Using VBA to count the number of rows used in a worksheet within the Excel Questions forums, part of the Question Forums category; Let's say that I want to count the number of rows on a particular worksheet (Sheet1) How would I do ...

  1. #1
    Board Regular
    Join Date
    Dec 2011
    Posts
    341

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

    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!

  2. #2
    Administrator starl's Avatar
    Join Date
    Aug 2002
    Location
    Everywhere!
    Posts
    4,969

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

    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.

  3. #3
    Board Regular
    Join Date
    Dec 2011
    Posts
    341

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

    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?

  4. #4
    Board Regular
    Join Date
    Dec 2011
    Posts
    341

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

    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)

  5. #5
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    14,864

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

    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

  6. #6
    Administrator starl's Avatar
    Join Date
    Aug 2002
    Location
    Everywhere!
    Posts
    4,969

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

    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")

  7. #7
    Board Regular
    Join Date
    Dec 2011
    Posts
    341

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

    Thanks everyone, works like a charm!

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com