Delete rows with specific column values

**koklok** · 10-16-2008, 09:10 AM

I’m pretty new to VBA and macros and I’m trying to delete rows if specific columns have certain values.
I tried different ways of doing it and I end up with a lot of headache.
I used other people codes but they were just to hard to understand what’s going on.
So I came up with this solution of solving the problem: I used Auto filter to select specific values and just deleted the rows.
Now I’m wondering why I don’t see other people use this solution?
Is there a major flaws in solving the problem this way?
If somebody has better and easier to understand solution will be glad to try using it.

What I did here is auto filter for column D and value 99 and deleted it and then column D and value blank and deleted it and finally column F and value 0 and deleted it.

Sub Macro2()
'
' Macro2 Macro
' Macro recorded 10/16/2008 by Preferred User
'
Cells.Select
Selection.Sort Key1:=Range("D2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("A1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=4, Criteria1:="99"
Rows("2:65536").Select
Selection.Delete Shift:=xlUp
Range("A1").Select
Selection.AutoFilter Field:=4
Selection.AutoFilter Field:=6, Criteria1:="0"
Rows("2:65536").Select
Selection.Delete Shift:=xlUp
Range("A1").Select
Selection.AutoFilter Field:=6
Selection.AutoFilter Field:=4, Criteria1:="="
Rows("2:65536").Select
Selection.Delete Shift:=xlUp
Range("A1").Select
Selection.AutoFilter Field:=4
'
End Sub

**Stuie** · 10-16-2008, 09:18 AM

Hi koklok,

This should save you getting a head ache,

this will go through every cell in column D and if the value = 99 it will delete the row.

give this a go

Sub deleteRows()
Dim delCount, count As Integer

count = Application.WorksheetFunction.CountIf(Range("D:D"), 99)

Dim cl As Range

StartDeleting:
For Each cl In Range("D:D")
    If cl.Value = 99 Then
        cl.EntireRow.delete
        delCount = delCount + 1
    End If
Next cl

If delCount <> count Then GoTo StartDeleting
End Sub

please remeber this checks all cells in col D so may take some time how ever you can enter a line to exit if a condition is met eg. if the cl value is blank then exit

if you want that in let me know and i will update

EDIT: and try to keep your code in the brackets so as not to get into trouble my mods

**koklok** · 10-16-2008, 09:36 AM

Stuie,

Thanks for the help but I’m afraid that this is not solving my problem

I need a code that will look into column D and Delete all values 99 (your codes does that)
But I also need next code to go thru column D and find all blank cells and delete the whole row.
3 rd item that I need is to go to column F and find all the values equal to 0 and delete the whole row.

I tried adjusting your formula but I’m getting message that is a duplicate.

What do I need to do to get this working?

**Stuie** · 10-16-2008, 09:42 AM

Ok then no probs,

try this,

Sub deleteRows()
Dim delCount, count As Integer

count = Application.WorksheetFunction.CountIf(Range("D:D"), 99) + _
        Application.WorksheetFunction.CountBlank(Range("D:D"))

Dim cl As Range

StartDeleting:
For Each cl In Range("D:D")
    If cl.Value = 99 Then
        cl.EntireRow.delete
        delCount = delCount + 1
        
    ElseIf cl.Value = "" Then
        cl.EntireRow.delete
        delCount = delCount + 1
    
    End If
Next cl

If delCount <> count Then GoTo StartDeleting

'Now start to delete from col F

Dim cl2 As Range
Dim delCount2, count2 As Integer

count2 = Application.WorksheetFunction.CountIf(Range("F:F"), 0)

StartDeleting2:
For Each cl2 In Range("F:F")
    If cl2.Value = 0 Then
        cl2.EntireRow.delete
        delCount2 = delCount2 + 1
        
    ElseIf cl2.Value = "" Then
        cl2.EntireRow.delete
        delCount2 = delCount2 + 1
    
    End If
Next cl2

If delCount2 <> count2 Then GoTo StartDeleting2

End Sub

i have just tacked the col F deleting on the end and put the if Blank delete in the origanl part