Announcement

Collapse
No announcement yet.

Vba Code To Count Rows

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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 this 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:
    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!

    Martin

  • #2
    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:
        lngRowsProject = Columns("AG:AG").SpecialCells(xlVisible).Rows.Count
    gives me the correct answer of 2.

    Bill

    Comment


    • #3
      Re: Vba Code To Count Rows

      Another method:
      Code:
      Sub TestButton1()
          NonBlankRange ("Sheet2!A:A")
      End Sub
      
      Sub NonBlankRange(sRange As String)
          Dim countNonBlank As Integer, myRange As Range
          Set myRange = Range(sRange)
          countNonBlank = Application.WorksheetFunction.CountA(myRange)
          MsgBox "Number of Non-Blank Cells: " & countNonBlank, , sRange
      End Sub

      Comment


      • #4


        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!

        Cheers!

        Martin

        Comment

        Working...
        X