Deleting Rows which don't meet specified conditions in VBA

[snideysimon]

Hi
I am wanting to write a macro which will delete all rows which do not meet a specified condition - the long hand version would be as follows:

If Range("J3") <> "Jan" Then Rows("3:3").Delete
If Range("J4") <> "Jan" Then Rows("4:4").Delete

Obviously I don't want to repeat this out 65000 times, is there a quicker way to do this

 

[PeregrinTook]

Simon,

Assuming you have headers in row 1:

Sub MyDelete()
Dim i As Long

For i = Cells(Rows.Count, "J").End(xlUp).Row To 2 Step -1
    If Cells(i, "J") <> "Jan" Then Rows(i).Delete
Next i

End Sub

But if you want to include row 1 in the loop change to this in line 4:

Cells(Rows.Count, "J").End(xlUp).Row To 1

HTH
PeregrinTook

 

[erik.van.geit]

Hi, snideysimon
Welcome to the Board !!!!!

If all data are next to each other without empties you can use
autofilter
custom: "is not equal" to "Jan"
select all rows
delete
show all data again

when using this in a macro you get

Sub deleterows_autofilter()
'header in J1
'data without gaps

Range("J1").AutoFilter Field:=1, Criteria1:="<>Jan" ', Operator:=xlAnd
Range("J2:J" & Range("J1").End(xlDown).Row).EntireRow.Delete
ActiveSheet.ShowAllData
End Sub

since there is no loop, this is very quick even with large ranges

kind regards,
Erik

 

[Vishaal]

its not my post but we have find the solution form your code

thanks

 

[Vishaal]

its not my post but we have find the solution form your code

thanks