VBA Deleting ListObjects Rows With Hidden Columns

[cr731]

I am writing a macro to delete all rows of a ListObject table not matching a specific value, so that the resulting table is left only with the value I want to keep. I am running into problems when the first or last column of the table is hidden, and cannot figure out what is going on or what the solution is.

A simplified version of my table is

[TABLE="class: grid, width: 384"]
<tbody>[TR]
[TD]Index
[/TD]
[TD]Col1
[/TD]
[TD]Col2
[/TD]
[TD]Col3
[/TD]
[TD]Name
[/TD]
[TD]Col5
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]xxx
[/TD]
[TD]xxx
[/TD]
[TD]xxx
[/TD]
[TD]Anne
[/TD]
[TD]xxx
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]xxx
[/TD]
[TD]xxx
[/TD]
[TD]xxx
[/TD]
[TD]Bob
[/TD]
[TD]xxx
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]xxx
[/TD]
[TD]xxx
[/TD]
[TD]xxx
[/TD]
[TD]Steve
[/TD]
[TD]xxx
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]xxx
[/TD]
[TD]xxx
[/TD]
[TD]xxx
[/TD]
[TD]Chris
[/TD]
[TD]xxx
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]xxx
[/TD]
[TD]xxx
[/TD]
[TD]xxx
[/TD]
[TD]John
[/TD]
[TD]xxx
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]xxx
[/TD]
[TD]xxx
[/TD]
[TD]xxx
[/TD]
[TD]Anne
[/TD]
[TD]xxx
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]xxx
[/TD]
[TD]xxx
[/TD]
[TD]xxx
[/TD]
[TD]Anne
[/TD]
[TD]xxx
[/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]xxx
[/TD]
[TD]xxx
[/TD]
[TD]xxx
[/TD]
[TD]Anne
[/TD]
[TD]xxx
[/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD]xxx
[/TD]
[TD]xxx
[/TD]
[TD]xxx
[/TD]
[TD]Anne
[/TD]
[TD]xxx
[/TD]
[/TR]
</tbody>[/TABLE]


Given the above, the column I want to delete based on is name. So my macro so far is

Sub DeleteRows()
    Application.DisplayAlerts = False
    Dim ListObj As ListObject
    Set ListObj = ActiveSheet.ListObjects(1)
    
    With ListObj
    
        .Range.Sort _
            Key1:=.ListColumns("Name"), _
            Order1:=xlAscending, _
            Header:=xlYes, _
            OrderCustom:=1, _
            MatchCase:=False, _
            Orientation:=xlTopToBottom
            
        .Range.AutoFilter Field:=.ListColumns("Name").Index, Criteria1:="<>" & "Anne"
        .DataBodyRange.SpecialCells(xlCellTypeVisible).Rows.Delete
        .AutoFilter.ShowAllData
    
    End With
    
    Application.DisplayAlerts = True
End Sub

That works fine, unless either the first or last column of the table is hidden. So say I create the table above, hide the "Index" column, and then run the macro, it deletes every column of the table (including headers) except for the hidden column. The same is true if the last column is hidden. However, columns hidden in the middle of the table do not cause similar problems.

Furthermore, it only is an issue for the first sorted item of the list. So back to my example, "Anne" is the first item in the sorted list. If I hide the first/last column and delete rows not matching "Bob," there is no issue.

I cannot understand why this is happening, as I thought this line

.DataBodyRange.SpecialCells(xlCellTypeVisible).Rows.Delete

Would tell the macro to only delete the DataBodyRange (not headers) and rows.

Any ideas on what the issue is with this?

Thanks

 

[Domenic]

Try replacing...

.DataBodyRange.SpecialCells(xlCellTypeVisible).Rows.Delete

with

.DataBodyRange.Delete

Hope this helps!

 

[cr731]

Try replacing...

.DataBodyRange.SpecialCells(xlCellTypeVisible).Rows.Delete

with

.DataBodyRange.Delete

Hope this helps!

Thanks! I'm a bit confused though, I thought that would delete the filtered cells as well, which is why I thought selecting visible cells only was necessary. Or does that only apply to hidden, not filtered, data?

 

[Domenic]

In this context, where the data is filtered, it will delete only visible cells.