Alternative to For Next Loop

[smpatty08]

I have this code;

For i = 2 To LastRow    If Cells(i, "D").Value = "" Then
        Range(Cells(i, "B"), Cells(i, "C")).ClearContents
    End If
Next i

What is the fastest way to get this done?

Thanks!

 

[Fluff]

If the cells are actually blank, rather than a formula showing "" try

    Range("D2", Range("D" & Rows.Count).End(xlUp)).SpecialCells(xlBlanks).Offset(, -2).Resize(, 2).Clear

 

[Greg Truby]

If the cells are actually blank, rather than a formula showing "" try

    Range("D2", Range("D" & Rows.Count).End(xlUp)).SpecialCells(xlBlanks).Offset(, -2).Resize(, 2).Clear[U][COLOR=#0000cd]Contents[/COLOR][/U]

You might also put in an ON ERROR RESUME NEXT just in case there aren't any blanks.

 

[Fluff]

You might also put in an ON ERROR RESUME NEXT just in case there aren't any blanks.

Excellent point Greg, this will now be

    On Error Resume Next
    Range("D2", Range("D" & Rows.Count).End(xlUp)).SpecialCells(xlBlanks).Offset(, -2).Resize(, 2).ClearContents
    On Error GoTo 0

 

[smpatty08]

Excellent point Greg, this will now be

    On Error Resume Next
    Range("D2", Range("D" & Rows.Count).End(xlUp)).SpecialCells(xlBlanks).Offset(, -2).Resize(, 2).ClearContents
    On Error GoTo 0

I should have mentioned that the blank cells in Column "D" will not be contiguous. Is there a way to do this with Autofilter instead of For Next Loop?

 

[Fluff]

Are your cells actually blank or are they formulae returning ""

 

[Marcelo Branco]

This should be very fast - assumes data in Sheet1

Sub aTest()
    Dim LastRow As Long
    
    With Sheets("Sheet1")
        LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
        .Range("B2:C" & LastRow).Value = _
            .Evaluate("=IF(D2:D" & LastRow & "="""","""",C2:B" & LastRow & ")")
    End With
End Sub

M.

 

[Greg Truby]

I should have mentioned that the blank cells in Column "D" will not be contiguous. Is there a way to do this with Autofilter instead of For Next Loop?

Fluff's (and Marcelo's for that matter) should work on non-contiguous ranges. You wouldn't need autofilter in order to pull this off.