Deleting rows with specific text with a macro

[bam]

I'm asking for help with the following.

I am using Excel 2003.
I have a time report from another system that I dump to excel.
The report contains many columns and about 10,000 rows of data.
I'd like to be able to delete rows that contain specific names.
Can someone provide me with the details of a macro that I could run that
would search for (for example) the names Bob, Sue, and Jim in column C and
then delete those rows. Even better if I can put the names for which I want
to delete rows -- in separate spreadsheet, table, etc. I do this dump from
the other system and then manually delete out names regularly. I am familar
with pivot tables, etc. but it would be helpful if I could get the names and
rows I don't want out of the file.

Any ideas about the best way to approach this would be appreciated,
Thanks in advance for any help here.

bmac

 

[Ron de Bruin]

Hi Bam

Try this

Advancedfilter is also a good option but start with this

The example below filter A1:A? In a sheet named "Sheet1"
Note: A1 is the header cell

And use as criteria all the cells in column A In a sheet named "Criteria".
Note:You can use also wildcards like *food* or *store if you want

Sub Delete_with_Autofilter_More_Criteria()
Dim rng As Range
Dim cell As Range
Dim Criteriarng As Range
Dim CalcMode As Long

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

With Sheets("Criteria")
Set Criteriarng = .Range("A1", .Cells(Rows.Count, "A").End(xlUp))
End With

For Each cell In Criteriarng

With Sheets("Sheet1")
.Range("A1", .Cells(Rows.Count, "A").End(xlUp)) _
.AutoFilter Field:=1, Criteria1:=cell.Value

With .AutoFilter.Range
Set rng = Nothing
On Error Resume Next
Set rng = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not rng Is Nothing Then rng.EntireRow.Delete
End With

.AutoFilterMode = False
End With

Next cell

With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With
End Sub


You can use EasyFilter to do it
http://www.rondebruin.nl/easyfilter.htm

Or use other code from this page
http://www.rondebruin.nl/delete.htm

 

[bam]

Thanks very much for the answer and the link!

Hi Bam

Try this

Advancedfilter is also a good option but start with this

The example below filter A1:A? In a sheet named "Sheet1"
Note: A1 is the header cell

And use as criteria all the cells in column A In a sheet named "Criteria".
Note:You can use also wildcards like *food* or *store if you want

Sub Delete_with_Autofilter_More_Criteria()
Dim rng As Range
Dim cell As Range
Dim Criteriarng As Range
Dim CalcMode As Long

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

With Sheets("Criteria")
Set Criteriarng = .Range("A1", .Cells(Rows.Count, "A").End(xlUp))
End With

For Each cell In Criteriarng

With Sheets("Sheet1")
.Range("A1", .Cells(Rows.Count, "A").End(xlUp)) _
.AutoFilter Field:=1, Criteria1:=cell.Value

With .AutoFilter.Range
Set rng = Nothing
On Error Resume Next
Set rng = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not rng Is Nothing Then rng.EntireRow.Delete
End With

.AutoFilterMode = False
End With

Next cell

With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With
End Sub


You can use EasyFilter to do it
http://www.rondebruin.nl/easyfilter.htm

Or use other code from this page
http://www.rondebruin.nl/delete.htm