# Counts rows in a range

• Hi all and thank you for such a great sight!

I have a report I deal with each day. The report has Apples, Oranges, Bananas, etc.... Sometimes the report only has 1 of the 10 and sometimes the report has all 10.

How would I count the rows in this range to see how many line items I have? Letting me know if it's 1 or 5 or 10?

tim

• Re: Counts rows in a range

it's hard to ay without seeing the layout. If the table is the only data on the sheet maybe

Code
``Msgbox Sheet1.UsedRange.Rows.Count``
• Re: Counts rows in a range

Thanks roy! I'll see what I can do...

Sometimes I will see you guys count rows and then take that number and use it somewhere in the code to do that amount of steps...

If I were as smart as you it wouldn't be a problem!

Thanks again Roy :p

Tim

• Re: Counts rows in a range

you can try this.

## Files

• Re: Counts rows in a range

Hey TJolly, are you looking for something like:

Code
``````Private Sub CommandButton1_Click()
Dim LastRow As Long
LastRow = Sheet1.Range("A65336").End(xlUp).Row + 1
Sheet1.Range("A" & LastRow).Value = "Another Row"
End Sub``````

Which finds the last Row and puts whatever you want in it.

• Re: Counts rows in a range

Not sure whether you want to count how many times each item appears in your list or just how many of the different items appear in the list, i.e. a count of unique items. If the latter, the formula

=SUMPRODUCT(1/COUNTIF(A1:A6,A1:A6&""))

[COLOR="Purple"]Regards,[/COLOR]
[COLOR="Purple"]Batman.[/COLOR]

• Re: Counts rows in a range

Hey there again!

I was checking with Brister on his code...

LastRow = Sheet1.Range("A65336").End(xlUp).Row + 1

it's actually saying 4 instead of 3...when I take the + 1 off it gives me the right number but deletes one of the words makeing it look like 2? Any thoughts on this? Nice code I must say!

You all have done enough that's for sure! Thanks again :p

• Re: Counts rows in a range

Ok...here is what I tried and it works perfect...

I just had to play with it for a minute I guess! Thanks again you Great Helpers!

Code
``Sheet1.Range("A" & lastrow).Offset(1, 0).Value = lastrow``

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!