×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Delete a Table Row in a Filtered Table
2

Delete a Table Row in a Filtered Table

Delete a Table Row in a Filtered Table

(OP)
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.

CODE --> VBA

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: https://excelmacromastery.com/vba-dictionary/
    ' Collections Reference: https://excelmacromastery.com/excel-vba-collections/
    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

RE: Delete a Table Row in a Filtered Table

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

CODE --> VBA

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

RE: Delete a Table Row in a Filtered Table

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

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

CODE -->

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

RE: Delete a Table Row in a Filtered Table

(OP)
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

RE: Delete a Table Row in a Filtered Table

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

combo

RE: Delete a Table Row in a Filtered Table

(OP)
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

RE: Delete a Table Row in a Filtered Table

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, .Delete Shift:=xlShiftUp 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

RE: Delete a Table Row in a Filtered Table

Quote (MattGreer)

... 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 ponder

---- Andy

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

RE: Delete a Table Row in a Filtered Table

(OP)

Quote (Andrezejek)

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

RE: Delete a Table Row in a Filtered Table

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"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!

RE: Delete a Table Row in a Filtered Table

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

RE: Delete a Table Row in a Filtered Table

Thx combo!

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"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!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close