Results 1 to 4 of 4

Thread: Conditional table row delete (table positioning issue)

  1. #1
    Board Regular
    Join Date
    Feb 2016
    Posts
    55
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Conditional table row delete (table positioning issue)

    Ran into an unexpected issue with conditional Table row delete. I have two tables with each checking the other for contents (with ISNUMBER & MATCH formula). I'm trying to delete all table rows from Table MASTER where ID value doesn't exist in Table QUERY - easy enough.



    After launching the code to do this (filtering "FALSE" and deleting the visible table area), I get an error message "This won't work because it would move cells in a table on your worksheet".

    The code will run in case I position those tables on top of each other (or split them over to different worksheets). Neither is great for readability (but doable). It just feels a little bogus error message as I still try to delete rows within the single table.

    Do you guys have any suggestions how to handle this better or should I just re-position those tables? On the flip side, keeping the code relatively simple is important too. Are there any good alternatives to ditching the autofilter and running some sort of a loop that deletes rows based on condition? Given it's a piece of a bit more extensive code, I'll probably just re-position tables instead of running the loop with the aim to skip autofilter (to keep the code relatively easy).

    Code:
    Sub DeleteRows()
    
    Dim Tbl As ListObject
    Set Tbl = ActiveSheet.ListObjects("MASTER")
    
    Application.DisplayAlerts = False
    
    Tbl.Range.AutoFilter Field:=2, Criteria1:="FALSE"
    Tbl.DataBodyRange.SpecialCells(xlCellTypeVisible).Delete
    
    End Sub
    Last edited by dotsent; Aug 25th, 2019 at 05:51 PM.

  2. #2
    MrExcel MVP AlphaFrog's Avatar
    Join Date
    Sep 2009
    Posts
    16,085
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    7 Thread(s)

    Default Re: Conditional table row delete (table positioning issue)

    Try this...

    Code:
    Sub DeleteRows()
        Dim rng As Range
        
        With ActiveSheet.ListObjects("MASTER")
            .Range.AutoFilter Field:=2, Criteria1:="FALSE"
            Set rng = .DataBodyRange.SpecialCells(xlCellTypeVisible)
            .Range.AutoFilter
            rng.Delete Shift:=xlShiftUp
        End With
        
    End Sub
    Paste your Excel data to the forum...
    MrExcel HTML Maker or Excel Jeanie

    How to post your vba code
    [CODE]your VBA code here[/CODE]
    The # button in the forum's editor will apply CODE tags around your selected text.

  3. #3
    Board Regular
    Join Date
    Feb 2016
    Posts
    55
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Conditional table row delete (table positioning issue)

    Quote Originally Posted by AlphaFrog View Post
    Try this...

    Code:
    Sub DeleteRows()
        Dim rng As Range
        
        With ActiveSheet.ListObjects("MASTER")
            .Range.AutoFilter Field:=2, Criteria1:="FALSE"
            Set rng = .DataBodyRange.SpecialCells(xlCellTypeVisible)
            .Range.AutoFilter
            rng.Delete Shift:=xlShiftUp
        End With
        
    End Sub
    Hi AlphaFrog, that looks to be working fine. Thanks!

    Could you briefly describe the difference though which makes this code run better? Are there any obvious risks involved? I assume the focus is on:

    Code:
    .Delete Shift:=xlShiftUp

  4. #4
    MrExcel MVP AlphaFrog's Avatar
    Join Date
    Sep 2009
    Posts
    16,085
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    7 Thread(s)

    Default Re: Conditional table row delete (table positioning issue)

    Deleting while filtered seems to be the snag with a table. This code stores the cells to delete, unfilters, then deletes.
    Paste your Excel data to the forum...
    MrExcel HTML Maker or Excel Jeanie

    How to post your vba code
    [CODE]your VBA code here[/CODE]
    The # button in the forum's editor will apply CODE tags around your selected text.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •