Run-time error '1004'? Excel VBA

Question

I have a problem with my code. I am trying to filter the range to exclude rows where in column K the value is equal to "March" and delete those rows. So all rows except the "March" ones will be displayed. The code runs well up to the point when it is trying to delete. .Offset(1, 0).SpecialCells.... It gives me the run-time error 1004

Public Sub RemoveRows()

Dim ws As Worksheet
Dim strSearch As String
Dim lRow As Long

strSearch = "March"

Set ws = Sheets("January")

With ws
    lRow = .Range("B" & .Rows.Count).End(xlUp).Row

    '~~> Remove any filters
    .AutoFilterMode = False

    '~~> Filter, offset(to exclude headers) and delete visible rows
    With .Range("K1:K" & lRow)
 .AutoFilter Field:=1, Criteria1:="=*" & strSearch & "*"
      .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete

    End With

    '~~> Remove any filters
    .AutoFilterMode = False
End With
End Sub

Gary's Student Gary's Student · Accepted Answer · 2015-03-05T12:04:36

You must put dots on your Range statements:

Public Sub RemoveRows()

Dim ws As Worksheet
Dim strSearch As String
Dim lRow As Long

strSearch = "March"
Set ws = Sheets("January")

With ws
        lRow = .Range("B" & .Rows.Count).End(xlUp).Row

    '~~> Remove any filters

        .AutoFilterMode = False

    '~~> Filter, offset(to exclude headers) and delete visible rows

        .Range("K1:K" & lRow).AutoFilter Field:=1, Criteria1:=strSearch
        .Range("K2:K" & lRow).Cells.SpecialCells(xlCellTypeVisible).EntireRow.Delete

    '~~> Remove any filters

    .AutoFilterMode = False
End With
End Sub

and the .offset() is not needed, just start at K2;
the internal With statement is not needed.

Run-time error '1004'? Excel VBA

2 Answers