Remove row based on cell value

Question

The code is designed to remove the column headers (after importing several files) for the data. But I get error: "1004" Which is "Application-defined or object-defined error". I've referenced different solutions on SO but to no avail.

Before I run this code snippit I remove the blank rows and have included this to show what does work as well and might even hold the key.

'Remove all rows with blanks first
Set wb = Workbooks(ThisWorkbook.Name)
'Remove blank rows based on if column 'B' is blank as a and B are filled when there is risk key showing
wb.Worksheets("BatchData").Activate
Columns("B:B").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
'Now to delete based on column headers!
Dim LastRow As Long
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
'Filter Column A and delete selection
'############# Error line below
ActiveSheet.Range("A1:" & A & ":" & LastRow).AutoFilter Field:=2, Criteria1:= _
        "=Item", Operator:=xlOr, Criteria2:="="
Selection.EntireRow.Delete

EDIT:

Amended code, some tweaks as per comments and also I had field "2" referenced and was trying to use 'A' which is 1.

Dim LastRow As Long
LastRow = wb.Worksheets("BatchData").Cells(Rows.Count, 1).End(xlUp).Row
'Filter Column A and delete selection
ActiveSheet.Range("A1:A" & LastRow).AutoFilter Field:=1, Criteria1:= _
        "=Item", Operator:=xlOr, Criteria2:="="

ActiveSheet.Range("$A$1:$A$" & LastRow).Offset(0, 0).SpecialCells _ (xlCellTypeVisible).EntireRow.Delete

Last line edit based on; VBA: How to delete filtered rows in Excel? but offset changed from 1,0 to 0,0

first you can use Set wb = ThisWorkbook. Second, when using LastRow it's better to use a certain sheet reference, like LastRow = wb.Worksheets("BatchData").Cells(Rows.Count, 1).End(xlUp).Row. Also, modify Range("A1:" & A & ":" & LastRow) to Range("A1:A" & LastRow) — Shai Rado
I have a hunch... Make a print of "A1:" & A & ":" & LastRow and add it to your post. — FDavidov
By the way, you are deleting a range within a column/row, not the whole column/row. — FDavidov

Shai Rado Shai Rado · Accepted Answer · 2016-10-25T11:42:35

Trying to clean-up your code a little, and to eliminate possible errors, try the code below, it filters Column A for "Item", then you can use one of 2 options:

Option 1: delete all rows, including header row.

Option 2: delete all rows, except header row.

Option Explicit

Sub DeleteFilteredRows()

Dim LastRow As Long

With ThisWorkbook.Worksheets("BatchData")

    ' remove blank rows based on if column 'B' is blank as a and B are filled when there is risk key
    .Columns("B:B").SpecialCells(xlCellTypeBlanks).EntireRow.Delete

    ' find last row in Column !
    LastRow = .Cells(Rows.Count, 1).End(xlUp).Row

    ' Filter Column A 
    .Range("A1:A" & LastRow).AutoFilter Field:=1, Criteria1:= _
            "=Item", Operator:=xlOr, Criteria2:="="

    ' option 1: delete also header row
    .Range("A1:A" & LastRow).SpecialCells _
        (xlCellTypeVisible).EntireRow.Delete

    ' option 2: if you want to keep the header row
    .Range("A1:A" & LastRow).Offset(1, 0).SpecialCells _
        (xlCellTypeVisible).EntireRow.Delete

End With

End Sub

Remove row based on cell value

1 Answers