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

    Code
    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!


    Martin

  • Re: Vba Code To Count Rows


    Hi Martin,


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


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


    gives me the correct answer of 2.


    Bill

  • 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


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


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


    Cheers!


    Martin