Results 1 to 3 of 3
  1. #1
    Lounger
    Join Date
    Dec 2011
    Posts
    33
    Thanks
    16
    Thanked 0 Times in 0 Posts

    Automate deleting filtered results

    Hi folks,

    What's the best way to automate the deletion of filtered results? In other words, I'd like my users to be able to run code which filters some database output (in this case, where the value in a column is 0) and then automatically deletes those entire rows.

    Thanks in advance for all your help,

    Beej

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Beej,

    This should do the trick.
    Code:
    Option Explicit
    
    Sub Macro1()
    
    'Delete rows containing zero in specified column.
    'Note: will also delete rows with blank in specified column.
    
       Dim lCurRow  As Long
       Dim lRowCntr As Long
       
       Sheets("Sheet1").Activate
       Cells(Rows.Count, 1).End(xlUp).Select '** Find last row with data
       lCurRow = ActiveCell.Row()
       
       For lRowCntr = lCurRow To 1 Step -1 'Work from bottom to top of list.
            '*** Note: the number 2 indicates Col B adjust as necessary
          If Cells(lRowCntr, 2) = 0 Then Rows(lRowCntr).Delete
       Next lRowCntr
       
        
    End Sub
    VBA Excel Delete Zero Rows.xlsm
    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    Lounger
    Join Date
    Apr 2012
    Location
    Abergavenny, Wales, UK
    Posts
    25
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Hi

    As an alternative to looping through the rows, you could use the folowing

    Code:
    Sub DeleteZeroRows()
    
    ' Delete all rows with Zero in column B of named sheet.
    ' Note this does not remove rows with Blank in column B
        With Sheets("Sheet1")
            .Range("A1:B1").AutoFilter Field:=2, Criteria1:="0"     ' Field 2 = Column B
            .Range("A1").CurrentRegion.Offset(1).SpecialCells(12).EntireRow.Delete
            .ShowAllData
            Selection.AutoFilter
        End With
    End Sub
    Attached Files Attached Files
    Regards
    Roger Govier
    Microsoft Excel MVP

Posting Permissions

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