[Excel] VBA - identify numbers of rows of selected areas

[sosasola]

Hi.
Here comes my problem: I have a few selected areas, the task is to identyfy the nuber of rows that had been selected. I think that the best solution would be to use array to store the numbers of rows, but if you have a better idea, tell me about it. For example:

I select areas: B4:C8 & A10:E14

the array should contain sth like: (4,5,6,7,8,10,11,12,13,14)

Duplicated rows should be ignored and should appear in the array only once (in case the different selected areas contained the same rows).

 

[Boller]

Dim area As Range, cell As Range, rwArray
[A:A].Insert
For Each cell In Intersect(Selection.EntireRow, Columns(1))
    cell = cell.Row
Next
[A:A].Sort Key1:=[A1], Order1:=xlAscending, Header:=xlNo
rwArray = Application.Transpose(Range([A1], [A65536].End(xlUp)))
[A:A].Delete

 

[sosasola]

Does not work as I expected. After step-by-step code analysis, it seems that this code inserts an extra collumn ([A:A].Insert) and (I have no idea why) ignores rest of the code.

 

[galileogali]

My answer:

Sub RowsDescribe()

Dim rngSel As Range, rngArea As Range, rngUnion As Range
Dim rngUnionCel As Range
Dim strArray As String
Dim i As Long
Dim collRows As New Collection

Set rngSel = Selection
Set rngUnion = rngSel.Areas(1)

If rngSel.Count = 1 Then Exit Sub


For Each rngArea In rngSel.Areas
Set rngUnion = Union(rngUnion, rngArea)

Next rngArea
On Error Resume Next
For Each rngUnionCel In rngUnion
collRows.Add rngUnionCel.Row, CStr(rngUnionCel.Row)

Next rngUnionCel

On Error GoTo 0
For i = 1 To collRows.Count
strArray = strArray & "," & collRows(i)
Next i

strArray = "{" & Right(strArray, Len(strArray) - 1) & "}"

MsgBox strArray

End Sub

GALILEOGALI

 

[sosasola]

Muchas gracias Galileogali, me has ayudado mucho.

 

[Boller]

Does not work as I expected. After step-by-step code analysis, it seems that this code inserts an extra collumn ([A:A].Insert) and (I have no idea why) ignores rest of the code.

The column is inserted temporarily (it is deleted at the end of the code).

The code stores the row numbers in an array (which is what you requested).
How do you want it to work?

Have added code to display each row number in separate message boxes :-

Dim area As Range, cell As Range, rng As Range, rwArray, i#
[A:A].Insert
For Each cell In Intersect(Selection.EntireRow, Columns(1))
    cell = cell.Row
Next
[A:A].Sort Key1:=[A1], Order1:=xlAscending, Header:=xlNo
Set rng = Range([A1], [A65536].End(xlUp))
rwArray = Application.Transpose(rng)
If rng.Cells.Count = 1 Then
    MsgBox rng
Else
    For i = 1 To UBound(rwArray)
        MsgBox rwArray(i)
    Next
End If
[A:A].Delete

This does the same thing but is "slicker" :-

Dim rng As Range, rwArray() As String, cell As Range, i#, str$
Set rng = Intersect(Selection.EntireRow, Columns(1))
ReDim rwArray(1 To rng.Cells.Count)
For Each cell In rng
    i = i + 1
    rwArray(i) = cell.Row
    MsgBox rwArray(i)
Next

If you want the row numbers displayed in one message box (like galileogali's code) :-

Dim rng As Range, rwArray() As String, cell As Range, i#, str$
Set rng = Intersect(Selection.EntireRow, Columns(1))
ReDim rwArray(1 To rng.Cells.Count)
For Each cell In rng
    i = i + 1
    rwArray(i) = cell.Row
Next
For i = 1 To UBound(rwArray)
str = str & "," & rwArray(i)
Next i
MsgBox Right(str, Len(str) - 1)

What do you want to do with the row numbers once they have been identified?

If you merely want to do what galileogali's code does (i.e. create a string of the row numbers and display them in a message box without storing them in an array) then :-

Dim cell As Range, str$
For Each cell In Intersect(Selection.EntireRow, Columns(1))
    str = str & "," & cell.Row
Next
MsgBox Right(str, Len(str) - 1)

 

[sosasola]

To Boller: Your code works as I wanted too. I think that there was some problem with my computer: I don't know why when I was testing your first code at home it did not work - I was trying several times to execute it and it was exiting the code after executing first line (if I marked this line as a comment, it exited on the next line, etc.). I've tried on another computer and now it is OK and everything works as I expected. You did a good work.

In fact an array which conains the numbers of selected rows is a part of a bigger code, there will be some other calculations based of this array, but I think I can manage to do the further operations by myself.

Thanks a lot for your help!