Forum Discussion
vba
I am trying to delete column C for words with "ongoing" i keep getting range of object worksheet failed error
Sub Delete_Rows_Based_On_Value()
Dim ws As Worksheet
Dim lRow As Long
Set ws = ThisWorkbook.Worksheets("4.04.21 Worksheet")
lRow = Cells(Rows.Count, 1).End(xlUp).Row
ws.Range("A2 & 1row").AutoFilter Field:=3, Criteria1:="ongoing"
Application.DisplayAlerts = False
ws.Range("A2 & 1row").SpecialCells(xlCellTypeVisible).Delete
Application.DisplayAlerts = True
On Error Resume Next
ws.ShowAllData
On Error GoTo 0
End Sub
3 Replies
First, I would suggest changing this:
lRow = Cells(Rows.Count, 1).End(xlUp).Row
as it will reference the active sheet. Which may not be an issue, but since the rest of the code qualifies the worksheet then this line probably should as well:
lRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
And change your references to the range from:
Range("A2 & 1row")
to this
Range("A2:A" & 1row)
JMB17 looks like the error is on ws.Range("A1:A" & 1Row).AutoFilter Field:=3, Criteria1:="ongoing"
- I overlooked the field number. You will need to double check your autofilter range and field number as there is no field 3 if the autofilter range is only Column A. So, the autofilter range will need to be "A1:C" & lRow or the field number should be 1.
