VBA count rows

[chaddres]

I need to count the number of rows in my pivot table. Normally, I would use: Count = Cells(Rows.Count, "A").End(xlUp).Row to get the number of rows with data in a column. I cannot figure out what to change to start counting at A4 and end at A24 in the example below. I can make the active cell A4 with Range("A4").Select, but don't know what to do from there. The number of rows will vary when I run the macro each time.

Any help or guidance is appreciated.

 

[6StringJazzer]

For your very specific situation

Count = Cells(Rows.Count, "A").End(xlUp).Row - 5

Your original expression gives the row number of the last nonempty cell. In this example it's row 25. One row is the Grand Total and four rows are headers, so subtract 5 to get the number of rows of data values, which in this example will give you 20.

 

[Alex Blakenburg]

See if any of these are of interest. I am counting the grand total line at this stage.
Add "- 1" to the lastRow line if you don't want to include that line.

Sub testRowCount()

    Dim ws As Worksheet
    Dim rng As Range
    Dim lastRow As Long
    Dim noOfRows As Long
  
    Set ws = ActiveSheet
  
    With ws
        lastRow = .Range("A" & Rows.Count).End(xlUp).Row
      
        ' Option 1
        noOfRows = lastRow - 4
      
        ' Option 2
        Set rng = .Range("A5:A" & lastRow)
        noOfRows = rng.Rows.Count
      
        ' Option 3
        noOfRows = .Range("A5:A" & lastRow).Rows.Count
    End With

End Sub

 

[Excelogist]

if the worksheet name is "Sheet1", the pivot table name is "PivotTable1",then here's the rows count:

Sheet1.PivotTables("PivotTable1").DataFields(1).DataRange.Rows.Count