Stop Sub if data has already been deleted - VBA

[Mr2017]

Hi

I'm using this Sub to delete rows where the number zero exists in column B.

However, if the Sub has already been run and the rows with the number zero in column B have been deleted, then the Sub produces an error.

Does anyone know how to exit the Sub IF there are no rows in column B with a zero value?

Please find below some simple sample data (for columns A and B):

1​	4​
2​	5​
3​	6​

Sub DeleteRows
With ActiveSheet 
  .Range("B1:B70").Autofilter 2, "0"
  .Autofilter.Range.Offset(1).EntireRow.Delete
  .Automodefilter = false
End With
End Sub

 

[Joe4]

Here is one way to suppress the error mesaage:

Sub DeleteRows()
    On Error Resume Next
    With ActiveSheet
        .Range("B1:B70").AutoFilter 2, "0"
        .AutoFilter.Range.Offset(1).EntireRow.Delete
        .Automodefilter = False
    End With
    On Error GoTo 0
End Sub

 

[Mr2017]

Hi Joe

When I tried to run that, it didn't actually delete the rows with the zero values?

I then tried to remove the 'On Error Resume Next' and 'On Error GoTo 0' lines, but then it gave me an error that said 'AutoFilter method of Range class failed'

This is despite the code working yesterday....

Do you know why that may be? I tried changing the format of the cells in column B to 'Number' with no decimal places, but that didn't work.....

Your thoughts would be greatly appreciated.

TIA

 

[Joe4]

I then tried to remove the 'On Error Resume Next' and 'On Error GoTo 0' lines, but then it gave me an error that said 'AutoFilter method of Range class failed'

To tell you truth, I set up a range with some zeroes in column B, and I could not get your original code to work. I thought maybe I had not set it up the same way as you did, and that if your code was working for you, what I added should handle that error.

So if the code was working for you originally, something changed, either your code or your data structure/content.

 

[Joe4]

After playing around with it, this seems to work for me:

Sub DeleteRows()
    With ActiveSheet
        .Range("B1:B70").AutoFilter 1, "0"
        .AutoFilter.Range.Offset(1).EntireRow.Delete
        .Range("B1:B70").AutoFilter Field:=1
    End With
End Sub

I think the issue was with the line:

.Range("B1:B70").AutoFilter 2, "0"

It looks like you are trying to filter on the second column in a one column range, which makes no sense.

 

[Mr2017]

Ok, thanks Joe - that worked!

The filter remained in place, after the rows with the zero values were deleted.

So I added this line to disable the filter, and was able to carry on with the rest of the code.

  If ActiveSheet.AutoFilterMode = True Then ActiveSheet.AutoFilterMode = False

Thanks a lot for your help!

 

[Joe4]

You are welcome.