How do you delete all cells but those containing a value?

[Aclare87]

Hey,

can someone give me a direct way of deleting all rows not containing particular text? I did it as a loop but it took forever and I had to run it several times because it kept jumping over values. There must be a simpler way, I just can't think of it yet! still new to VBA :/

Thanks!

Dim DateColumn As Range
Dim cell As Range
Set DateColumn = ActiveSheet.Range("a2:a623")
'remove unwanted rows (not = M XXXX MTD)
For Each cell In DateColumn
If Not cell.Value = "January 2013 MTD" Then
cell.EntireRow.delete
End If
Next cell

 

[njimack]

This should take less than a second...

Sub Test()
    
Dim Last_Row As Long
Application.ScreenUpdating = False
With ActiveSheet
    Last_Row = .Range("A" & Rows.Count).End(xlUp).Row
    With .Range("A1:A" & Last_Row)
        .AutoFilter
        .AutoFilter Field:=1, Criteria1:="<>January 2013 MTD", Operator:=xlAnd
        .SpecialCells(xlCellTypeVisible).EntireRow.Delete
    End With
    .ShowAllData
End With
Application.ScreenUpdating = True
End Sub

 

[Jonmo1]

Try using the AutoFilter

This is assuming A1 is a Header

Dim DateColumn As Range
Set DateColumn = ActiveSheet.Range("a1:a623")
DateColumn.AutoFilter Field:=1, Criteria1:="<>January 2013 MTD"
DateColumn.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
DateColumn.AutoFilter

 

[Aclare87]

Great, thanks Neil.

Don't totally understand everything in that code but that's what the learning process is all about!

Thanks again.

 

[Aclare87]

Perfect, thanks. this is what I needed. Thanks!

 

[njimack]

Great, thanks Neil.

Don't totally understand everything in that code but that's what the learning process is all about!

Thanks again.

Glad to be of assistance. All it does is autofilter the data for all cells that don't contain the specified text, and then delete those visible rows.

 

[nuked]

Just a note on your first question, you need to loop backwards when deleting rows.

So instead of for...each, you'd need:

For i = 623 to 2 Step - 1
' test YourRange.Cells(i,1)
' YourRange.Cells(i,1).EntireRow.Delete
next i

This will prevent rows being skipped.

 

[Aclare87]

Hey, can someone explain what the Field=1 option means? I don't understand what the excel help guide is saying.

Thanks