CODEWITHSUNDEEP
excelvba
Tourless
Tourless

Reputation: 27

If 0 clear contents for multiple columns

I'm wondering how to make the following code work for multiple columns (D:P)? I've already tried adding & ":"P" & "65536" to the range, without success.

For i = 5 To Range("D" & "65536").End(xlUp).Row Step 1
If Application.WorksheetFunction.CountIf(Range("D" & i), "0") = 1 Then
Range("D" & i).ClearContents
End If
Next i

Upvotes: 0

Views: 51

Answers (2)

K.Dᴀᴠɪs
K.Dᴀᴠɪs

Reputation: 10139

Loop through the actual column and use the Find() method to perform your search. If your value exists in that range (range = column), then you can clear the contents that way.

Sub test()

    ' Just for illustration on the columns
    Const D& = 4:   Const P& = 16

    Dim ws As Worksheet, col As Long
    Set ws = ThisWorkbook.Worksheets(1)

    For col = D To P
        If Not ws.Columns(col).find(What:="0", LookAt:=xlWhole) Is Nothing Then
            ws.Columns(col).ClearContents
        End If
    Next col

End Sub

Upvotes: 0

Alex de Jong
Alex de Jong

Reputation: 1267

You can use Range("D5:P65536").Replace What:=0,Replacement:="" to Replace all at once.

Upvotes: 1

Related Questions