AutoFilter returning to 1st Range when trying to apply filter to different Range

Question

`I have a looping command that is trying to delete entire rows when there is a blank cell in a column. The cell appears to be blank but has something in it that is failing my ISBLANK formula. Left over from formulas I assume.

I am not a developer by any means, just toying around with VBA to organize some data so please excuse any messy/terrible style coding. My issue appears after the first execution of deleting rows after the ws.ShowAllData. The command deletes the rows as expected however when I am hoping it applies the next filter to B25:B43, the AutoFilter is applying it back to the original B45:B50 range even though I am specifying B25:B43.

I had inserted a Range("B45:B50").Clear before the 2nd range thinking that it would clear the first filter however it actually deleted my data in that range. It did allow the command to continue to the second range and delete correctly. I only realized it deleted my cell data so the Range.Clear halfway worked.

Below is the code I tried`

Sub DeleteRows()


Do
  Dim ws As Worksheet
      Set ws = ActiveSheet

  ws.Range("B45:B50").AutoFilter Field:=1, Criteria1:=""
Application.DisplayAlerts = False
  ws.Range("B46:B50").SpecialCells(xlCellTypeVisible).Delete
Application.DisplayAlerts = True
  ws.ShowAllData

This is where the issue starts, the rows were deleted from range above but when trying to apply B25:B43 autofilter, the filter returns to B45:B50 above, this is where I added the Range.Clear and it did apply the next range below correctly however it deleted my data.

Range("B25").Select
   ws.Range("B25:B43").AutoFilter Field:=1, Criteria1:=""
Application.DisplayAlerts = False
   ws.Range("B26:B43").SpecialCells(xlCellTypeVisible).Delete
Application.DisplayAlerts = True
   ws.ShowAllData


ws.Range("B1:B23").AutoFilter Field:=1, Criteria1:=""
   Application.DisplayAlerts = False
ws.Range("B2:B23").SpecialCells(xlCellTypeVisible).Delete
   Application.DisplayAlerts = True
ws.ShowAllData
   Range("A1").Select

ActiveSheet.Previous.Select
Loop Until ActiveSheet.Name = "Systems"

 End Sub

Answer 1

Check there are rows to delete otherwise .Delete will fail.

Sub DeleteRows()

    Const RNGS = "B45:B50,B25:B43,B1:B23"
    Dim ws As Worksheet, rng As Range, rngDel As Range, r As Long
    
    Set ws = ActiveSheet
    If ws.Name = "Systems" Then Exit Sub
    Do
        With ws
            For Each rng In ws.Range(RNGS).Areas
                With rng
                    .AutoFilter Field:=1, Criteria1:=""
                    r = .Rows.Count - 1
                    Set rngDel = Nothing
                    On Error Resume Next
                    Set rngDel = .Resize(r).Offset(1).SpecialCells(xlCellTypeVisible)
                    On Error GoTo 0
                    If rngDel Is Nothing Then
                        Debug.Print ws.Name, "No blanks, rng.Address"
                    Else
                        Debug.Print ws.Name, "Deleted", rngDel.Address
                        rngDel.EntireRow.Delete
                    End If
                    .AutoFilter
                End With
            Next
        End With
        Set ws = ws.Previous
    Loop Until ws.Name = "Systems"
End Sub