Vba Code To Count Rows

  • Hi,

    I’m looking for a VBA code which counts the number of rows which contains data.
    I was looking in the forum and found [thread=29867]this thread[/thread].

    Unfortunately the code is not returning the correct number of rows
    I was playing a bit around but never got the correct result

    1. Sub countRows()
    2. Dim lngRowsProject As Long
    3. lngRowsProject = Columns("AG:AG").SpecialCells(xlVisible).Rows.Count ' returns me 21 (column AH has 21 rows, but column AG has just 2)
    4. lngRowsProject = Range("AG").CurrentRegion.Rows.Count ' returns me 43 (the total numbers of rows in the spreadsheet are 43)
    5. MsgBox "The number of rows is " & lngRowsProject
    6. End Sub

    Does anyone know how to change the code that it’s just returning the number of rows in column AG???

    Thanks a lot in advance!


  • Re: Vba Code To Count Rows

    Hi Martin,

    When I filter Column AG and there are only 2 meeting the criteria, then your code

    1. lngRowsProject = Columns("AG:AG").SpecialCells(xlVisible).Rows.Count

    gives me the correct answer of 2.


  • Re: Vba Code To Count Rows

    Another method:

  • Re: Vba Code To Count Rows

    Thanks guy, you’re hint’s were very useful!
    As I need the number of rows as a variable for other macros I modified it to the following code

    Dim countnonblank As Integer, myRange As Range
    Set myRange = Columns("AG:AG")
    countnonblank = Application.WorksheetFunction.CountA(myRange)

    as a separate macro it works, so I hope it will work as a variable!