Get non-contiguous row numbers from a selection

[daverunt]

Struggling here.

I need to be able to get the actual row numbers of non contiguous selection on one sheet of a workbook - preferably via a loop. The rows each include a sparkline.
The Selected rows will then be deleted.
I can then use that number to relate to the sparkline data column on another sheet in order to delete the columns within the same loop.

It's the extraction of actual row numbers from the selection that I am struggling with.

Any links/pointers appreciated

 

[jasonb75]

Row as a range, or as a number?

For Each r In Selection.Rows
    MsgBox r.Row
Next

 

[footoo]

If the selection is not in one column :

For Each r In Intersect(Columns(1), Selection.EntireRow)
    MsgBox r.Row
Next

 

[daverunt]

Many thanks Jason and Footoo both work great.

 

[jasonb75]

It took me a while to figure out the difference between the results of the 2 methods, the difference is not an easy one to explain, @footoo's method works better 'If there are non contiguous selections in any given row'. This is difficult to explain accurately, depending on the method of selection, 2 selected cells next to each other may not be a contiguous selection.

As an example, if you used a set range instead of Selection, Range("A1,B1") would show row 1 twice in my method but only once in @footoo's while Range("A1:B1") would show only once in both methods. In the first example where the cells are comma separated, the selection is non-contiguous.

Using selection, the way that the range would be interpreted by the code could be unpredictable, with the first example above being the result of selecting by ctrl click, the second by click and drag or shift and cursor key.

Hope that makes some sense, I've retyped it 3 times and I'm still lost

 

[daverunt]

Both work to get the row numbers correctly, which was necessary to work out the related data column. The user isn't going to be doing any odd selections.
They'll either select the rows by cell in Column A or the entire row from the left.
So thanks again both for the start.

After that deleting the actual rows individually was proving to be a non starter as the selection only deleted the first row. Presumably the deletion of one row screws up the original selection.

So I've let Excel do the work and gone with Selection.Delete for the Rows and
I've converted the row numbers to related column letters to create a range selection for column deletion.
E.G Range("A:A,C:C:XX").Select

So far it seems to be working ok.

' Selections on worksheet Report
    For Each r In Selection.Rows
    
'Subtract 2 from the row numbers because of the headers
     ColNum = r.Row - 2
     ColumnLetter = Split(Cells(1, ColNum).Address, "$")(1)
     
     cols = ColumnLetter & ":" & ColumnLetter
     colsx = cols & "," & colsx
     
  Next
  
'remove trailing comma  
    colsx = Left(colsx, Len(colsx) - 1)
     
 'Delete the selected rows on the Report sheet
    Selection.EntireRow.Delete
    
 'Activate Data sheet and select and delete columns
    Worksheets("Data").Activate
    Range(colsx).Select
    Selection.Delete Shift:=xlToLeft