Hi everyone,
I have a part of my code where I delete some rows based on information found with autofilters.
On the first filter, when it does not meet the conditions, the code just skips it as it should.
On the second filter, the error handling is not working.
Can you please help me understand why the second autofilter does not handle the error?
Thank you.
I have a part of my code where I delete some rows based on information found with autofilters.
On the first filter, when it does not meet the conditions, the code just skips it as it should.
On the second filter, the error handling is not working.
VBA Code:
blankcolumn = Cells(1, 1).End(xlToRight).Offset(0, 1).Column
Cells(2, blankcolumn).Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-11],'Draft'!C[-11]:C[9],19,0)"
Selection.AutoFill destination:=Range(Cells(2, blankcolumn), Cells(pendinglastrow, blankcolumn))
Application.Calculation = xlCalculationAutomatic
Range(Cells(1, 1), Cells(pendinglastrow, blankcolumn)).Select
Selection.AutoFilter
ActiveSheet.Range(Cells(1, 1), Cells(pendinglastrow, blankcolumn)).AutoFilter Field:=blankcolumn, Criteria1:="<>" & 0, _
operator:=xlFilterValues
On Error GoTo test:
Range(Cells(2, blankcolumn), Cells(pendinglastrow, blankcolumn)).SpecialCells(xlCellTypeVisible).Select
Selection.EntireRow.Delete
test:
ActiveSheet.ShowAllData
Columns(blankcolumn).ClearContents
Cells(1, 1).Select
pendinglastrow = Cells(1000000, 1).End(xlUp).Row
Cells(2, blankcolumn).Select
ActiveCell.FormulaR1C1 = _
"=IF(VLOOKUP(RC[-11],'Draft'!C[-11]:C[4],14,0)="""",""x"",VLOOKUP(RC[-11],'Draft'!C[-11]:C[4],14,0))"
Selection.AutoFill destination:=Range(Cells(2, blankcolumn), Cells(pendinglastrow, blankcolumn))
Application.Calculation = xlCalculationAutomatic
Range(Cells(1, 1), Cells(pendinglastrow, blankcolumn)).Select
Selection.AutoFilter
ActiveSheet.Range(Cells(1, 1), Cells(pendinglastrow, blankcolumn)).AutoFilter Field:=blankcolumn, Criteria1:="=0", _
operator:=xlAnd
On Error GoTo newerror:
Range(Cells(2, blankcolumn), Cells(pendinglastrow, blankcolumn)).SpecialCells(xlCellTypeVisible).Select 'error here
Selection.EntireRow.Delete
newerror:
ActiveSheet.ShowAllData
Columns(blankcolumn).ClearContents
Cells(1, 1).Select
Can you please help me understand why the second autofilter does not handle the error?
Thank you.