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


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

    Sub countRows()
    Dim lngRowsProject As Long
    lngRowsProject = Columns("AG:AG").SpecialCells(xlVisible).Rows.Count  ' returns me 21 (column AH has 21 rows, but column AG has just 2)
    lngRowsProject = Range("AG").CurrentRegion.Rows.Count  ' returns me 43 (the total numbers of rows in the spreadsheet are 43)
    MsgBox "The number of rows is " & lngRowsProject
    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

    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!



Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!