How do I set up a counter cell?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Valpocandi - 11 Sep 2007 18:48 GMT

I need to set up a cell that counts the number of rows I am using, and sets
that number in a cell. Is there a way to make sure to check to see if the row
has a value in the first cell. I know i need to set up some type of if
statement for this, but I'm not sure how to go about it. Can anyone help me?

Reply to this Message

Mike H - 11 Sep 2007 18:56 GMT

Hi,

Right click the sheet tab, view code and paste this in. Change the cell
address to whatever you want:-

Private Sub Worksheet_Change(ByVal Target As Range)
Range("A1").Value = ActiveSheet.UsedRange.Rows.Count
End Sub

Mike

> I need to set up a cell that counts the number of rows I am using, and sets
> that number in a cell. Is there a way to make sure to check to see if the row
> has a value in the first cell. I know i need to set up some type of if
> statement for this, but I'm not sure how to go about it. Can anyone help me?

Reply to this Message

PCLIVE - 11 Sep 2007 18:59 GMT

What should be included in your row count? If the first row contains no
data, should that row be counted?
Assuming that you want to omit the first row in your count only if it
contains no data, then maybe something like this:

=IF(COUNTA(1:1)=0,COUNTA(A:A)-1,COUNTA(A:A))

Keep in mind that this will count rows if column A of the row contains data.
If column A contains no data, then it is assumed that the row is empty.

HTH,
Paul

>I need to set up a cell that counts the number of rows I am using, and sets
> that number in a cell. Is there a way to make sure to check to see if the
> row
> has a value in the first cell. I know i need to set up some type of if
> statement for this, but I'm not sure how to go about it. Can anyone help
> me?

Reply to this Message