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

sosasola

New Member
Joined
Jan 26, 2007
Messages
29
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)

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

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Boller

Banned
Joined
Apr 11, 2006
Messages
2,328
Code:
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

New Member
Joined
Jan 26, 2007
Messages
29
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

Well-known Member
Joined
Oct 14, 2005
Messages
748
My answer:

Code:
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

New Member
Joined
Jan 26, 2007
Messages
29
Muchas gracias Galileogali, me has ayudado mucho.
 

Boller

Banned
Joined
Apr 11, 2006
Messages
2,328
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 :-

Code:
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" :-

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
    MsgBox rwArray(i)
Next

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

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 :-

Code:
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

New Member
Joined
Jan 26, 2007
Messages
29
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!
 

Forum statistics

Threads
1,181,410
Messages
5,929,774
Members
436,688
Latest member
sunnyBNH013

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top