count used rows
 

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

count used rows
 

On Oct 3, 12:07 pm, glenn wrote:
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.

count used rows
 

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
"Andrew" wrote:

On Oct 3, 12:07 pm, glenn wrote:
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.