Delete a Table Row in a Filtered Table 2

[MattGreer]

When I filter a table and try to delete just that row, Excel always asks me if I want to delete the row on the entire worksheet. The only solution I know of is I have to clear the filters, delete that row in that table, and then re-apply the filters. I posted about this a while back and got my answer; no way to do this. I had a thing to do today and I decided that, doggone it, I'm going to get this working and be done with it.

I'm not saying this code is good nor accurate, but it appears to be working at the moment. I only have one filter applied in my table tho. If one of y'all wants to test it out, I'd appreciate the feedback. Also, if there's a way to shorten/improve the code, I'd appreciate the suggestions as well. There's commented out code at the bottom that I left in there in case it's useful for anyone.

Sub DeleteFilteredTableRowFinal()
    'Include a reference to 'Microsoft Scripting Runtime' for the Scripting.Dictionary stuff

    Dim rCell As Range
    Dim iRow As Long, iTblRow As Long, DeleteRow As Long
    Dim tbl As ListObject
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim i As Long
    Dim dict As New Scripting.Dictionary
    Dim k As Variant
    
    Set wb = ThisWorkbook
    Set ws = wb.ActiveSheet
    Set tbl = ws.ListObjects("Table1")
    Set rCell = ActiveCell
        
    Application.ScreenUpdating = False
    ' Step 1: Check to make sure you're in a table
        
    ' Step 2: Save out all the filters in the table
    ' Scripting Dictionary References: [URL unfurl="true"]https://excelmacromastery.com/vba-dictionary/[/URL]
    ' Collections Reference: [URL unfurl="true"]https://excelmacromastery.com/excel-vba-collections/[/URL]
    Set dict = New Scripting.Dictionary
    
    For i = 1 To tbl.AutoFilter.Filters.Count
        If tbl.AutoFilter.Filters(i).On Then
'            Debug.Print "Storing Filter on column #" & i & ", Name: " & tbl.ListColumns(i).Name
            dict.Add tbl.ListColumns(i).Name, _
            Array(i, _
                tbl.AutoFilter.Filters(i).Criteria1, _
                tbl.AutoFilter.Filters(i).Operator)
        End If
    Next i
    
    ' Step 3: Reset the autofilter in the table
    tbl.AutoFilter.ShowAllData
    
    ' Step 4: Get the row number that you're going to delete
    iRow = rCell.Row 'Absolute row number
    iTblRow = tbl.Range.Row 'Row number of the header column
    DeleteRow = iRow - iTblRow
    Debug.Print DeleteRow
    
    ' Step 5: Delete that row
    tbl.ListRows(DeleteRow).Delete
    
    ' Step 6: Reapply the filters to the table
    For Each k In dict.Keys()
        tbl.Range.AutoFilter Field:=dict(k)(0), Criteria1:=dict(k)(1)
    Next k
    
    

' Saved/old code for reference:

'    Debug.Print "Count of entries in the dict: " & dict.Count
    
' Loop through all the filters and display
'    For i = 1 To tbl.AutoFilter.Filters.Count
'        If tbl.AutoFilter.Filters(i).On Then
'            Debug.Print "Column Index: " & i
'            Debug.Print "Column Name: " & tbl.ListColumns(i).Name
'            Debug.Print "Criteria: " & tbl.AutoFilter.Filters(i).Criteria1
'            Debug.Print "Operator: " & tbl.AutoFilter.Filters(i).Operator
'        End If
'    Next i
    
'    Dim v As Variant
'    For Each k In dict.Keys()
'        Debug.Print k, Join(dict(k), ",") 'Prints out all the details for the key
'        Debug.Print dict(k)(0)
'        Debug.Print dict(k)(1)
'        Debug.Print dict(k)(2)
'        For Each v In dict(k)
'            Debug.Print v
'        Next
'    Next k
    
    Application.ScreenUpdating = True
    
    Set rCell = Nothing
    Set tbl = Nothing
    Set wb = Nothing
    Set ws = Nothing

End Sub

Thanks!!


Matt

 

[MattGreer]

Throw this in there for a "is this cell in a table" check:

Function U_IsActiveCellInTable() As Boolean
    'Function returns true if active cell is in a table and
    'false if it isn't.
    
    Dim rngActiveCell
    
    On Error GoTo NotInTable
    
    Set rngActiveCell = ActiveCell
    U_IsActiveCellInTable = True

    'Statement produces error when active cell is not in a table.
    rngActiveCell = (rngActiveCell.ListObject.Name <> "")
    
Function_Finish:
    On Error GoTo 0
    Exit Function

NotInTable:
    U_IsActiveCellInTable = False
    Resume Function_Finish

End Function

Thanks!!


Matt

 

[combo]

Actually you can shorten the function:
[pre]Function U_IsActiveCellInTable() As Boolean
U_IsActiveCellInTable = Not ActiveCell.ListObject is Nothing
End Function[/pre]

Using Application.DisplayAlerts you can delete rows without warning. Without storing filter settings, the whole code could be (assuming working with worksheet 1):

Sub test()
If ActiveCellTable <> False Then
    Application.DisplayAlerts = False
    With Worksheets(1).ListObjects(ActiveCellTable)
        With .Range
            For i = .Rows.Count To 1 Step -1
                If .Rows(i).Hidden Then .Rows(i).Delete
            Next i
        End With
        .AutoFilter.ShowAllData
    End With
    Application.DisplayAlerts = True
End If
End Sub

Function ActiveCellTable() As Variant
If ActiveCell.ListObject Is Nothing Then
    ActiveCellTable = False
Else
    ActiveCellTable = ActiveCell.ListObject.Name
End If
End Function

combo

 

[MattGreer]

If I read that code correctly, it looks like it deletes all the hidden rows? Perhaps I misunderstand.

The point of my subroutine is to delete the active row (where the active cell is) in a filtered table without having to manually unfilter the table, delete the row, and then re-apply the filter to the table. I want to keep all the hidden rows. I just want to delete that one row that I'm looking at.

The use case for this is when I've got a table full of information, but some of it might be redundant or no longer needed. I want to delete the row I've identified but I don't want to go have to reapply all the filters that I had to get to that point.

Very nice on the 'Is Nothing' for identifying if a cell is in a table.



Thanks!!


Matt

 

[combo]

Thanks, I didn't read the posts too carefully. Anyway, [tt]Application.DisplayAlerts = False[/tt] blocks excel warnings and the code executes default action, in this case confirm delete.

combo

 

[MattGreer]

Of course. But the issue with deleting a row in a filtered table is if you leave the filters in place, Excel will delete the entire sheet row which may be undesirable if you have other tables/data on the same worksheet.


Thanks!!


Matt

 

[combo]

I see your point. It is not possible to move cells in filtered range or table, the only choice is to delete entire row. (Otherwise, for range, [tt].Delete Shift:=xlShiftUp[/tt] would work for visible row, same for filtered, if would be assessible for Delete.) Storing filters data and deleting in unfiltered table seems to be the only solution.

combo

 

[Andrzejek]

... if you have other tables/data on the same worksheet.

Since you have a need to Delete a row in a table, would it be better to have just one table per worksheet

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[MattGreer]

Since you have a need to Delete a row in a table, would it be better to have just one table per worksheet ponder

Not only that, but you wouldn't be able to have *any* information next to a table in Excel, because as I mentioned, attempting to delete a row in a filtered table causes Excel ask you to delete the *entire* row, not just the table row.

A lot of times it makes sense to have other information on worksheet in addition to a table.

Thanks!!


Matt

 

[SkipVought]

If your tables are Structured Tables, there is a feature of STs that deletes a Table Row rather than a Worksheet Row.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[combo]

For filtered range/table the only possibility is to delete entire row. No option for table row in UI, if this is forced by code, excel refuses and displays the above message.

combo

 

[SkipVought]

Thx combo!

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!