CODEWITHSUNDEEP
vbaexcelfor-looprow
user2837456
user2837456

Reputation: 1

Handle a single cell when looping through rows of a selected range

I have the below code to loop through each row of a selected range. However when only a single cell is selected, the code loops through each row in the worksheet rather than just dealing with the one instance.

What do I need to do so that the for loop only handles the one row when a single cell is selected?

Dim myRange as Range
Dim currRow as Range

Set myRange = Selection

For Each currRow In myRange.Rows.SpecialCells(xlCellTypeVisible)
    MsgBox currRow.Address
Next currRow

Thanks,

Upvotes: 0

Views: 569

Answers (2)

ARich
ARich

Reputation: 3279

Just add an if statement to the code to handle hidden rows: 

    Dim myRange As Range
    Dim currRow As Range

    Set myRange = Selection

    For Each currRow In myRange
        If currRow.EntireRow.Hidden = False Then
            'Place your code here.
            Debug.Print currRow.Address
        End If
    Next currRow

Upvotes: 0

L42
L42

Reputation: 19737

I don't know the reason why the code behave as such. It looks ok.
But to get what you want, try this:

Dim myRange As Range
Dim currRow As Range

Set myRange = Selection

If myRange.Rows.count = 1 And myRange.Columns.count = 1 Then
    For Each currRow In myRange.Rows
        MsgBox currRow.Address
    Next currRow
Else
    For Each currRow In myRange.Rows.SpecialCells(xlCellTypeVisible)
        MsgBox currRow.Address
    Next currRow
End If

hope this works.

Upvotes: 1

Related Questions