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?

    Thank you again in advance.


  • 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

    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


  • Re: Counts rows in a range

    you can try this.

  • Re: Counts rows in a range

    Hey TJolly, are you looking for something like:

    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


    can be adapted to suit.


  • 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 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!

    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!