Automate deleting filtered results

Topic

New Reply

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

Reply | Quote

Replies

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

34469-VBA-Excel-Delete-Zero-Rows
HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Beej,

This code will look in a specified column for zeros then deletes the entire row. In this sample, the search is in column 1 but it can be changed to any column. The number of rows to search is automatically determined by finding the last row using values in column 1.

HTH,
Maud

34470-deleterow1 34471-deleterow2

Code:

Public Sub DeleteRows()
[COLOR=”#008000″]’DECLARE AND SET VARIABLES[/COLOR]
Dim LastRow As Integer  [COLOR=”#008000″]’LAST ROW NUMBER[/COLOR]
Dim ZeroCol As Integer  [COLOR=”#008000″]’COLUMN CHECKED FOR ZEROS[/COLOR]
LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
ZeroCol = 1  [COLOR=”#008000″]’CHANGE TO COLUMN TO BE CHECKED FOR ZEROS[/COLOR]
[COLOR=”#008000″]’———————————————————
‘SEARCH ZEROCOL FOR “0” AND DELETE ENTIRE ROW[/COLOR]
For I = 2 To LastRow  [COLOR=”#008000″]’START ROW 2 ALLOW FOR HEADER[/COLOR]
    If Cells(I, ZeroCol).Value = 0 Then
        Cells.Rows(I).EntireRow.Delete
    End If
Next I
End Sub

If there can be zeros in more than one column, then use this code

Code:

Public Sub DeleteRows()
[COLOR=”#008000″]’DECLARE AND SET VARIABLES[/COLOR]
Dim cell As Range
Dim rng As Range
Set rng = ActiveSheet.UsedRange
[COLOR=”#008000″]’———————————————————
‘SEARCH ZEROCOL FOR “0” AND DELETE ENTIRE ROW[/COLOR]
For Each cell In rng
    If cell.Value = 0 Then cell.EntireRow.Delete
Next cell
End Sub

Reply | Quote

RG,

You were posting while I was coding! I see the wisdom in starting from the bottom and working up.

Reply | Quote

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

Reply | Quote