get selected row numbers with filter

[masterx7]

hi,

I have a code for getting the row numbers of selected cells (I only select cells from column A)

now,
If column A not filtered and I'm dragging the mouse to select several row at once, the code works.
If column A is filtered and I'm choosing the cells one by one with CTRL pressed, the code works.

but, if column A is filtered and I'm dragging the mouse so select several rows, the code doesn't work.
"objSelectionArea.Rows.Count" needs to be value of 1 and not higher.

for example, if filter gives me 2 lines (line 2 and line 9)
the "objSelectionArea.Rows.Count" gives me 10, but if I don't drag the mouse and choose with CTRL and mouse each line, "objSelectionArea.Rows.Count" gives me 1.

what can be done?

Private Sub CommandButton1_Click()


Dim objSelection As Range
Dim objSelectionArea As Range
Dim objCell As Range
Dim intRow As Integer
Dim intActualRow As Integer


Set objSelection = Application.Selection


For Each objSelectionArea In objSelection.Areas


For intRow = 1 To objSelectionArea.Rows.Count Step 1

Set objCell = objSelectionArea.Rows(intRow)
intActualRow = objCell.Row
Next
Next





End Sub

 

[cerfani]

each object selection area in your example would only be 1 row though... if you select only row 2 and 9 by using ctrl... then you have 2 areas... each with 1 row

if you count the areas you will get 2... a selection represents an area or multiple areas and each area can have a different amount of rows... each time you add to your selection with ctrl you create an area

https://msdn.microsoft.com/en-us/vba/excel-vba/articles/range-areas-property-excel

objSelection.Areas.Count is the number of times you pressed Ctrl+ Clicked Down Then Up on something

 

[JoeMo]

See if this does what you want. Run the code after you have made your selection of cells.

Sub WhichRows()
Dim Rw As Range
If Selection.Count = 1 Then
    MsgBox Selection.Row
Else
    On Error Resume Next
    For Each Rw In Selection.SpecialCells(xlCellTypeVisible)
        MsgBox Rw.Row
    Next Rw
End If
End Sub

 

[masterx7]

thanks Joe, it did the trick.
last thing, I use the row number for some code, but when I reach the last selected line I need to write a different code.
how can I ask if this is the last row?

 

[JoeMo]

thanks Joe, it did the trick.
last thing, I use the row number for some code, but when I reach the last selected line I need to write a different code.
how can I ask if this is the last row?

You are welcome.

Here's a modification that determines the last row in the original range selection.

Sub WhichRows()
Dim Rw As Range, lastRw As Long
If Selection.Count = 1 Then
    MsgBox Selection.Row
Else
    On Error Resume Next
    For Each Rw In Selection.SpecialCells(xlCellTypeVisible)
        If Rw.Row >= lastRw Then lastRw = Rw.Row
        MsgBox Rw.Row
    Next Rw
End If
MsgBox "Last row in the selected range is: " & lastRw
End Sub

 

[masterx7]

You are welcome.

Here's a modification that determines the last row in the original range selection.

Sub WhichRows()
Dim Rw As Range, lastRw As Long
If Selection.Count = 1 Then
    MsgBox Selection.Row
Else
    On Error Resume Next
    For Each Rw In Selection.SpecialCells(xlCellTypeVisible)
        If Rw.Row >= lastRw Then lastRw = Rw.Row
        MsgBox Rw.Row
    Next Rw
End If
MsgBox "Last row in the selected range is: " & lastRw
End Sub

hi, jo, this is not good for me.
I need to write different code when I reached last row in Selection.SpecialCells(xlCellTypeVisible)

 

[JoeMo]

hi, jo, this is not good for me.
I need to write different code when I reached last row in Selection.SpecialCells(xlCellTypeVisible)

Not sure I understand. Do you mean you want to stop when the last row is reached and add some code to do something before the line:
MsgBox Rw.Row

??

 

[masterx7]

Not sure I understand. Do you mean you want to stop when the last row is reached and add some code to do something before the line:
MsgBox Rw.Row

??

yes. I have a code to create txt file with lines I marked from excel. the problem is that it opens blank row at the end.
so in last row I want to put ";" so new line won't be created.
for that I need to now when it reached last line in Selection.SpecialCells(xlCellTypeVisible)

 

[JoeMo]

yes. I have a code to create txt file with lines I marked from excel. the problem is that it opens blank row at the end.
so in last row I want to put ";" so new line won't be created.
for that I need to now when it reached last line in Selection.SpecialCells(xlCellTypeVisible)

Try this modification. Add your code where the comment line is.

Sub WhichRows()
Dim Rw As Range, Rws As Long, ct As Long
If Selection.Count = 1 Then
    MsgBox Selection.Row
Else
    On Error Resume Next
    Rws = Selection.SpecialCells(xlCellTypeVisible).Count
    For Each Rw In Selection.SpecialCells(xlCellTypeVisible)
        ct = ct + 1
        If ct = Rws Then
            MsgBox "Reached Last Row. That's Row " & Rw.Row
            'add your code here
        End If
        MsgBox Rw.Row
    Next Rw
End If
End Sub

 

[masterx7]

Try this modification. Add your code where the comment line is.

Sub WhichRows()
Dim Rw As Range, Rws As Long, ct As Long
If Selection.Count = 1 Then
    MsgBox Selection.Row
Else
    On Error Resume Next
    Rws = Selection.SpecialCells(xlCellTypeVisible).Count
    For Each Rw In Selection.SpecialCells(xlCellTypeVisible)
        ct = ct + 1
        If ct = Rws Then
            MsgBox "Reached Last Row. That's Row " & Rw.Row
            'add your code here
        End If
        MsgBox Rw.Row
    Next Rw
End If
End Sub

thanks man, exactly what I wanted.
have a good one.