Loop through named range and select row and columns

Question

I have a named range that looks like:

For each row where column 2 equals zero I want to white out the row from columns A:F (the six columns). What I have does not work as it selects the entire named range and whites the whole thing out when the if statement becomes true.

Sub modFinishFinancialEstimate()

Dim myrange As Range
Dim ws As Worksheet

Set myrange = Range("actual_cost_of_svc")
Set ws = ActiveSheet

ws.Select

For i = myrange.Rows(1).row To myrange.Rows.Count
    MsgBox "The Count of services is " & Cells(i, 2).Value
    If Range("B" & i).Value = 0 Then
        MsgBox "The value is " & Cells(i, 2).Value & " and will be whited out"
        For Each col In myrange.Columns
            With Selection.Font
                .ThemeColor = xlThemeColorDark1
                .TintAndShade = 0
            End With
            ActiveWorkbook.ws.Sort.SortFields.Add Key:=Range( _
    myrange), SortOn:=xlSortOnCellColor, Order:=xlAscending, DataOption:= _
    xlSortNormal
            With Selection.Sort
                .SetRange myrange
                .Header = xlGuess
                .MatchCase = False
                .Orientation = xlTopToBottom
                .SortMethod = xlPinYin
                .Apply
            End With
        Next col
    End If
Next

End Sub

The problem I encounter with the code above is that it checks the first row only and then exits the sub.

SJR SJR · Accepted Answer · 2019-08-01T17:08:00

The first row will be

msgbox myrange.rows(1).row

You don't need to Select anything.

Alternatively, you could make your loop relative, i.e. the ith cell of myrange rather than the ith cell of the worksheet.

Loop through named range and select row and columns

1 Answers