count used rows

's Computer Specifications

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

's Computer Specifications

On Oct 3, 12:07 pm, glenn <gl...@nospam.swklmn.com> 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.

's Computer Specifications

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 <gl...@nospam.swklmn.com> 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.
>
>

Thread Tools
Show Printable Version Email this Page
Rate This Thread
Rate This Thread:

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
datatable rows count and dataview count differ	John Hui	Microsoft ADO .NET	4	3rd Aug 2010 10:26 PM
VBA to count rows from specific cell and insert rows	Valerie	Microsoft Excel Programming	3	26th Nov 2007 10:14 PM
Counters?, Count Rows?, Get Rows? in an Excel Macro	=?Utf-8?B?Q19UQjI0?=	Microsoft Excel Programming	2	3rd Feb 2005 04:29 PM
Re: how do i count rows, and count them by color or a highlight? blue.	Frank Kabel	Microsoft Excel Worksheet Functions	0	9th Sep 2004 10:05 PM
I am adding a new row to the datagrid dynamically but if i use the Count property of Item it is not showing the count of the new rows being added	Praveen Balanagendra via .NET 247	Microsoft ASP .NET	2	6th Jun 2004 08:16 AM