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

Aclare87

New Member
Joined
Mar 23, 2012
Messages
24
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
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

njimack

Well-known Member
Joined
Jun 17, 2005
Messages
7,764
This should take less than a second...

Code:
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

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Try using the AutoFilter

This is assuming A1 is a Header

Code:
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

New Member
Joined
Mar 23, 2012
Messages
24
Great, thanks Neil.

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

Thanks again.
 

Aclare87

New Member
Joined
Mar 23, 2012
Messages
24

ADVERTISEMENT

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

njimack

Well-known Member
Joined
Jun 17, 2005
Messages
7,764
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

Well-known Member
Joined
Mar 20, 2013
Messages
883
Just a note on your first question, you need to loop backwards when deleting rows.

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

Code:
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

New Member
Joined
Mar 23, 2012
Messages
24
Hey, can someone explain what the Field=1 option means? I don't understand what the excel help guide is saying.

Thanks
 

Watch MrExcel Video

Forum statistics

Threads
1,123,143
Messages
5,599,975
Members
414,354
Latest member
Flaxarn

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top