Solved

Filter for zeros and delete contents -vba

Posted on 2011-05-03
266 Views
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"
``````
0
Question by:Seamus2626

LVL 30

Accepted Solution

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

Sid
0

LVL 33

Assisted Solution

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
``````
0

LVL 33

Expert Comment

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

LVL 30

Expert Comment

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
``````

Sid
0

LVL 30

Expert Comment

AutoFilter is the most fastest way of doing it.

Sample Attached.

Sid
Delete-Rows-VBA.xls
0

Author Closing Comment

Thanks guys!
0

Featured Post

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…