selected rows 1

[Johnny42]

I would like to loop all selected rows on an active sheet.
the rows can be continuous or a ctrl select....can anyone suggest ?

 

[SkipVought]

Hi,

Deja vous???

dim r as range
for each r in selection.rows
...
next

Skip,

_{[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken!}

 

[Johnny42]

Thanks Skip,

how can I just count the rows....if the user ctrl selected 2 cells on the same row ?

 

[SkipVought]

    Dim a As Range, areaCount, i
    areaCount = Selection.Areas.Count
    If areaCount <= 1 Then
        MsgBox "The selection contains " & _
            Selection.Rows.Count & " rows."
    Else
        i = 1
        For Each a In Selection.Areas
            MsgBox "Area " & i & " of the selection contains " & _
                a.Rows.Count & " rows."
            i = i + 1
        Next a
    End If

Skip,

_{[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken!}

 

[Johnny42]

Sorry must be missing it...

If user selects :

A1,B2,A4,B4,C6 I need rows selected = 4
I dont see how I can get this from your above post...

 

[Zathras]

I think Skip may be taking his post-prandial nap.

You did say "selected rows" and not "selected cells." That kind of led us down the garden path.

Try something like this:

Sub test()
  MsgBox CountSelectedRows(Selection)
End Sub

Function CountSelectedRows(ARange As Range) As Integer
Dim c As Range
Dim a(100) As Boolean
Dim i As Integer
  For Each c In ARange
    a(c.Row) = True
  Next c
  For i = LBound(a) To UBound(a)
    If a(i) Then
      CountSelectedRows = CountSelectedRows + 1
    End If
  Next i
End Function

If you have more than 100 rows that may have selections, you will need to modify the array size accordingly.

 

[Johnny42]

Thank you both...

 

[SkipVought]

Hey JOHHNIE,

Do I have to hold your hand and wipe your nose???

Connect the dots, son!

    Dim a As Range, areaCount, i
    areaCount = Selection.Areas.Count
    If areaCount <= 1 Then
        MsgBox "The selection contains " & _
            Selection.Rows.Count & " rows."
    Else
        i = 0
        For Each a In Selection.Areas
            i = i + 1
 '           MsgBox "Area " & i & " of the selection contains " & _
                a.Rows.Count & " rows."
        Next a
MsgBox i
    End If

Skip,

_{[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken!}

 

[Zathras]

Easy there, Skip old buddy.

Try your code after selecting A1,C1,E1,B3 and D3. Your code announces "5", but only 2 rows are involved.

I think in that example, Johnny42 would want to see "2" not "5."

 

[SkipVought]

Johnny42,

I am sorry for my un-called-for outburst.

Z, thanx for pointing out the error of my ways.



Skip,

_{[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken!}

 

[Zathras]

Happy to oblige, Skip. We all have our bad days now and then. Not all of us own up to them, however.

I feel privileged to call you my friend.

 

[fumei]

A

http://wwww,

sniff, that is so nice. And I mean that with absolutely no sarcasm. Good on you both.

Gerry
See my Paintings and Sculpture