VBA Auto Filter: No Cells were found-CodePudding

Please help.

I want to filter my table by field 13 for values that are <0.5 and delete those visible rows, but I get the error code that no cells were found, even though there clearly are values that meet that criteria.

Dim lo As ListObject
Set lo = Worksheets("Aluminum Futures").ListObjects("PF")
    
    lo.Range.AutoFilter Field:=13, Criteria1:="<0.5"

    Application.DisplayAlerts = False
       lo.DataBodyRange.SpecialCells(xlCellTypeVisible).Delete
    Application.DisplayAlerts = True

    lo.AutoFilter.ShowAllData

What am I doing wrong?

CodePudding user response：

I don't think you're doing anything wrong here. I used the code, it ran the first time and worked. Every subsequent run through and error saying nothing found like you were saying. Because they were all deleted already. So I did this. It's a quick fix. Granted it's going to do that anytime there is an error when deleting though. Make sure 13 is actually the column you need too like people are mentioning.

Sub Button1_Click()
Dim lo As ListObject
Set lo = Worksheets("Sheet1").ListObjects("Table1")

lo.Range.AutoFilter Field:=13, Criteria1:="<0.5"

On Error GoTo NothingFound
    Application.DisplayAlerts = False
    lo.DataBodyRange.SpecialCells(xlCellTypeVisible).Delete

NothingFound:
    Application.DisplayAlerts = True
    lo.AutoFilter.ShowAllData
End Sub

CodePudding user response：

You could try searching for your column to get proper column index. As your code works when I tested.

FilterRow = Rows("1:1").Find(What:="ID", LookAt:=xlWhole).Column

lo.Range.AutoFilter Field:=FilterRow, Criteria1:="<0.5"