Alternative to Entirerow.Clearcontents

Question

I am looking to find a row in my data which corresponds to a reference cell named Val4 and delete that range. I have this which works for deleting the entire Row or just the content of that row but I would like to delete range ("L:M") instead of the entire row. Here is a segment of my code:

    Sheets("Sheet1").Select

    Last = Cells(Rows.Count, "L").End(xlUp).Row
    For I = Last To 1 Step -1
        If (Cells(I, "L").Value) = Val4 Then
   'Cells(I, "L").ClearContents ' USE THIS TO CLEAR CONTENTS BUT NOT DELETE ROW
            'Cells(I, "A").EntireRow.Delete USE THIS TO DELETE ENTIRE ROW
            GoTo NextSheet1
        End If
    Next I

Thanks for the help.

Ross

`Sheets("Sheet1").Range(Sheets("Sheet1").Cells(I,"L"),Sheets("Sheet1").Cells(I,"M")).ClearContents` — Scott Craner, Dec 20 '16 at 17:29
http://stackoverflow.com/questions/38455405/what-is-the-difference-between-value-and-clearcontents — Ricky, Dec 20 '16 at 22:58

score 0 · Accepted Answer · answered Dec 20 '16 at 18:25

you could use

Intersect(Sheets("Sheet1").Range("L:M"), Sheets("Sheet1").Rows(i)).ClearContents

but to speed up your code you could use AutoFilter() and not to loop through cells:

With Sheets("Sheet1") '<--1 reference your sheet
    With .Range("M1", .Cells(.Rows.Count, "L").End(xlUp)) '<--| reference its column L:M cells from row 1 down to column "L" last not empty row
        .AutoFilter field:=1, Criteria1:=Val4 '<--| filter referenced range on its 1st column (i.e. column "L") with Val4
        If Application.WorksheetFunction.Subtotal(103, .Resize(, 1)) > 1 Then '<--| if any cell filtered other than header one (in row1)
            .Resize(.Rows.Count - 1).Offset(1).SpecialCells(xlCellTypeVisible).ClearContents '<--|clear filtered referenced cells
        End If
    End With
    .AutoFilterMode = False
End With

Thanks for the help SOLVED. – FrenchieRab Dec 21 '16 at 07:58 — FrenchieRab, Dec 21 '16 at 07:58

Alternative to Entirerow.Clearcontents

1 Answers1