Results 1 to 10 of 10
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Count rows in multiple selection! (Excel 2000 >)

    Hi all,

    If a user selects multiple non -consecutive rows, how can I get the code to count how many rows are selected?

    Can someone help me modify this code to count the correct number of rows.
    <pre>Option Explicit
    Sub CountRows()
    Dim NumRows As Long
    NumRows = Selection.Rows.Count
    MsgBox NumRows
    End Sub
    </pre>

    Tx
    Regards,
    Rudi

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Count rows in multiple selection! (Excel 2000 >)

    If you look up Rows in the online help, you'll see how it is done:

    Dim n As Long
    Dim rng As Range
    For Each rng In Selection.Areas
    n = n + rng.Rows.Count
    Next rng
    MsgBox n

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Count rows in multiple selection! (Excel 2000 >)

    <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>
    When will I learn to trust the help resources!

    Maybe you should <img src=/S/cop.gif border=0 alt=cop width=15 height=24> me on this too, just like you did with Option Explicit - <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>
    Regards,
    Rudi

  4. #4
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Count rows in multiple selection! (Excel 2000 >)

    Hi Hans,

    Can I pick your brains (or is that brawn) further.

    I know its off the topic of the original, but say I want to count the amount of rows if only cells are selected. How can I get 11 rows as an answer of stead of 13 cells. Your code is now saying 13 as a answer?
    Regards,
    Rudi

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Count rows in multiple selection! (Excel 2000 >)

    <pre>Option Explicit
    Sub CountRows()
    Dim n As Long
    Dim rRows As Range
    Dim rng As Range
    Dim rCell As Range
    For Each rng In Selection.Areas
    For Each rCell In rng
    If rRows Is Nothing Then
    Set rRows = rCell.EntireRow
    Else
    Set rRows = Union(rRows, rCell.EntireRow)
    End If
    Next rCell
    Next rng
    For Each rng In rRows.Areas
    n = n + rng.Rows.Count
    Next rng
    MsgBox n
    End Sub</pre>


    Steve

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Count rows in multiple selection! (Excel 2000 >)

    From your original post, I had - incorrectly - assumed that you selected entire rows. The following rather clumsy code does what you ask. There are probably better ways.

    Sub CountRows()
    Dim rng As Range
    Dim colRows As New Collection
    Dim lngRow As Long
    Dim lngCount As Long

    On Error Resume Next
    For Each rng In Selection.Areas
    For lngRow = 1 To rng.Rows.Count
    colRows.Add rng.Cells(lngRow, 1).Row, "R" & rng.Cells(lngRow, 1).Row
    Next lngRow
    Next rng

    lngCount = colRows.Count
    MsgBox lngCount
    End Sub

  7. #7
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Count rows in multiple selection! (Excel 2000 >)

    Wow, shall i dare ask for a laymans explanation!

    Tx, it works great, but even stepping through the code does not reveal too much of how it is doing the job. This Union function I've seen before too. i'll need to read up about this one!

    Tx a mil.. Steve
    Regards,
    Rudi

  8. #8
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Count rows in multiple selection! (Excel 2000 >)

    >> From your original post, I had - incorrectly - assumed that you selected entire rows.
    You are not incorrect... I did not specify that! After I got your answer I thought I'd take the concept further and ask if it would be possible to count rows if cells were selected too!

    Sorry for the confusion!!

    Thx for the code. It looks easier than Steves, though I have not analysed it yet!
    Cheers
    Regards,
    Rudi

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Count rows in multiple selection! (Excel 2000 >)

    <hr>shall i dare ask for a laymans explanation! <hr>
    Why not try, it might get you an answer (if you don't ask, people may not answer...).

    I will assume you would asK

    'I dimmed variables like Hans' code. I added the rRows to contain a new range object
    <pre> Dim n As Long
    Dim rRows As Range
    Dim rng As Range
    Dim rCell As Range

    'Go thru each "area" (contiguous range) of the selection
    For Each rng In Selection.Areas
    'within each "area" go through each cell
    For Each rCell In rng
    'if rRows is nothing (this only occurs initially),
    If you union "nothing" with a range you get a runtime error
    If rRows Is Nothing Then
    'Set the range object as the entire of the cell
    Set rRows = rCell.EntireRow
    Else
    'If the range is not nothing
    'combine the current "range of rows" with the new row
    Set rRows = Union(rRows, rCell.EntireRow)
    End If
    Next rCell
    Next rng
    'At this point rRows is the range of all the rows in your "selection"
    'This eliminates "overlapping", since the Union does not grow if you add the same row again
    'You could look at the address of rrows if desired:
    msgbox rRows.address
    'This is just like Hans' code only it looks in the Areas of rRows not the selection
    For Each rng In rRows.Areas
    n = n + rng.Rows.Count
    Next rng
    MsgBox n</pre>


    Does that help?

    Steve

  10. #10
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Count rows in multiple selection! (Excel 2000 >)

    Great Steve. That makes it a lot clearer.
    Many thanx for your enthusiastic reply!
    Regards,
    Rudi

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •