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.


    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.


  • 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&""))


    can be adapted to suit.

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