asked on

Filter for zeros and delete contents -vba

Hi,

I have used the recorder to et the code to select all entries on column K with a zero, what would be the next step to delete all those entries?

Thanks
Seamus

Rows("1:1").Select
    Range("H1").Activate
    Selection.AutoFilter
    Range("K1").Select
    Selection.AutoFilter Field:=11, Criteria1:="0"

Open in new window

ASKER CERTIFIED SOLUTION

SiddharthRout

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

jppinto

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jppinto

Here's a sample file that will look on column A and delete all rows where is value is 0.

jppinto
Delete-Rows-VBA.xlsm

SiddharthRout

jp, If you want to use loops then I would recommend a slightly faster version of your code. I have just modified it :)

Sub Delete_Rows()
    Application.ScreenUpdating = False
 
    Dim i As Long, LastRow As Long, rng As Range
    
    LastRow = Sheets("Sheet1").Range("K" & Rows.Count).End(xlUp).Row
    
    For i = LastRow To 2 Step -1
        If Sheets("Sheet1").Cells(i, "K").Value = 0 Then
            If rng Is Nothing Then
                Set rng = Rows(i)
            Else
                Set rng = Union(rng, Rows(i))
            End If
        End If
    Loop
    
    rng.Delete shift:=xlUp
    
    Application.ScreenUpdating = True
End Sub

Open in new window

Sid

SiddharthRout

AutoFilter is the most fastest way of doing it.

Sample Attached.

Sid
Delete-Rows-VBA.xls

Seamus2626

ASKER

Thanks guys!