Alternate rows get skipped when looping over rows in an excel table to remove them using VBA-CodePudding

I have an excel table. I filter the table for a specific project (filter based on first column). Because rows are non-contiguous the .EntireRow.Delete method does not work. A workaournd I found to be partially working is to loop over the selected rows after filtering and removing rows. If I just do row.select in a for loop it looks fine, but when I change to row.delete only alternate rows get deleted. Is there a way to fix this?

Here is my code:

Sub deleteRows()

ActiveSheet.ListObjects("myTable").AutoFilter.ShowAllData

ActiveSheet.ListObjects("myTable").Range.AutoFilter Field:=1, Criteria1:="Project X"

Set selection = Range("myTable").SpecialCells(xlCellTypeVisible).EntireRow

For Each row in selection
    row.Delete 'using row.select here would work fine and select every row, but using row.delete skips rows and only alternate rows get deleted
Next row

Exit Sub

CodePudding user response：

Here, this is what you're looking for.
you can't set selection. you can however set a range variable.
A correct alternative to Set Selection = ... would be range("somerange").select ...
However, it would still be easier to just apply the change directly.

Option Explicit
Sub deleteRows()
    
    Dim RW As Long
    Dim RG As Range
    
    ActiveSheet.ListObjects("myTable").AutoFilter.ShowAllData
    
    ActiveSheet.ListObjects("myTable").Range.AutoFilter Field:=1, Criteria1:="Project X"
    
    Set RG = Range("myTable").SpecialCells(xlCellTypeVisible)
    ActiveSheet.ListObjects("myTable").AutoFilter.ShowAllData
    RG.Delete
    
End Sub

Start:

After MAcros: