count used rows

[glenn]

Hi all

How do I count the number of rows that hold some value in a worksheet? I
have tried several way but I always get also the empty rows in the count.

I just want the non-empty rows to be counted.

Something like

RowsCount = Worksheet(1).Rows.Count ?

thanks

 

[Andrew]

Hi all

How do I count the number of rows that hold some value in a worksheet? I
have tried several way but I always get also the empty rows in the count.

I just want the non-empty rows to be counted.

Something like

RowsCount = Worksheet(1).Rows.Count ?

thanks

At the end of each row, set one cell as =sum(A1:Z1)
Then set the next cell after it to =IF(AA1<>"",1,0) - this will be
equal to one unless all values are ""
Then count the last column. I'm sure you can also do this in VBA as
well.

 

[Joel]

Sub test()

Count = 1
For RowCount = 1 To Rows.Count
If WorksheetFunction.Sum(Rows(RowCount)) > 0 Then
Count = Count + 1
End If
Next RowCount


End Sub