• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 331
  • Last Modified:

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

0
Seamus2626
Asked:
Seamus2626
  • 3
  • 2
2 Solutions
 
SiddharthRoutCommented:
Please try this

Sub Sample()
    Worksheets("Sheet1").Range("K1").AutoFilter Field:=11, Criteria1:="0"
    Application.DisplayAlerts = False
    ActiveSheet.UsedRange.Offset(1, 0).Resize(ActiveSheet.UsedRange.Rows.Count - 1).Rows.Delete
    Application.DisplayAlerts = True
End Sub

Open in new window


Sid
0
 
jppintoCommented:
I would try this code on your file.

jppinto
Sub Delete_Rows()
    
    Application.ScreenUpdating = False
 
    Dim i As Long
    i = 2
    Do Until i > Cells(Rows.Count, "K").End(xlUp).Row

        If Cells(i, "K").Value = 0 Then
            Rows(i).delete
        Else
            i = i + 1
        End If
    Loop
    Application.ScreenUpdating = True
 
End Sub

Open in new window

0
 
jppintoCommented:
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
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
SiddharthRoutCommented:
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
0
 
SiddharthRoutCommented:
AutoFilter is the most fastest way of doing it.

Sample Attached.

Sid
Delete-Rows-VBA.xls
0
 
Seamus2626Author Commented:
Thanks guys!
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now