OnError Resume Next after autofilter

[Eclektics]

Hi all,

I'm trying to handle an event with an on error line but it seems to be skipping it. I've checked my options and it's still set to only break on unhandled errors so I'm not sure what's causing it. My main aim is to filter a bunch of data and delete the visible rows, on occasion the value I'm filtering for won't exist in the data set so I want a procedure to handle this and move on to the rest of the process.

Here's my code, I've tried different options from resume next to GoTo and still no joy, the first part relating to CCN works but then breaks at the second section with a runtime 1004 error 'no cells visible'and highlights line '

.Range("$A$2:$AW$" & LRow).SpecialCells(xlCellTypeVisible).EntireRow.Delete' when debugging.​

Any help anyone can offer would be greatly appreciated.

Sub Test()
Dim ws As Worksheet
Set ws = Sheet2
Dim LRow As Long
LRow = ws.Cells(ws.Rows.Count, "A:A").End(xlUp).Row
With ws
    On Error GoTo 66
    .Range("$A:$AW").AutoFilter field:=14, Criteria1:="CCN*"
    .Range("$A$1:$AW$" & LRow).SpecialCells(xlCellTypeVisible).Copy Destination:=ws.Range("A1")
    .Range("$A$2:$AW$" & LRow).SpecialCells(xlCellTypeVisible).EntireRow.Delete
66
    .ShowAllData
End With
With ws
    .Range("$A:$AW").AutoFilter field:=27, Criteria1:="*PECB*"
On Error GoTo nextline:
    .Range("$A$2:$AW$" & LRow).SpecialCells(xlCellTypeVisible).EntireRow.Delete
nextline:
    .ShowAllData
End With
End Sub

 

[ranman256]

you turned 'On Error GoTo nextline' into a label with the colon.

remove it.

On Error GoTo nextline

​

Nextline IS a label.
And you need to end the code if you DONT want nextline to run. As is, it runs it anyway.

On Error GoTo nextline
With ws
    .Range("$A:$AW").AutoFilter field:=27, Criteria1:="*PECB*"
    .Range("$A$2:$AW$" & LRow).SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With
exit sub

nextline:
    ws.ShowAllData
End Sub

<strike></strike>
​

 

[Eclektics]

Thanks for your quick response I've amended the code as you've advised but it still errors on the same line and doesn't skip?

 

[Fluff]

VBA can only handle one error at a time, so if you had an error here

    On Error GoTo 66
    .Range("$A:$AW").AutoFilter field:=14, Criteria1:="CCN*"
    .Range("$A$1:$AW$" & LRow).SpecialCells(xlCellTypeVisible).Copy Destination:=ws.Range("A1")
    .Range("$A$2:$AW$" & LRow).SpecialCells(xlCellTypeVisible).EntireRow.Delete
66

as you haven't cleared the error, the next error handler won't work.
Have a look here for info on errors & error handling http://excelmatters.com/2015/03/17/on-error-wtf/

Try

Sub Test()
Dim ws As Worksheet
Set ws = Sheet2
Dim LRow As Long
LRow = ws.Cells(ws.Rows.Count, "A:A").End(xlUp).Row
With ws
    .Range("$A:$AW").AutoFilter field:=14, Criteria1:="CCN*"
    On Error Resume Next
    .Range("$A$1:$AW$" & LRow).SpecialCells(xlCellTypeVisible).Copy Destination:=ws.Range("A1")
    .Range("$A$2:$AW$" & LRow).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    On Error GoTo 0
    .ShowAllData
    
    .Range("$A:$AW").AutoFilter field:=27, Criteria1:="*PECB*"
    On Error Resume Next
    .Range("$A$2:$AW$" & LRow).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    On Error GoTo 0
    .ShowAllData
End With
End Sub