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

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

--
Regards Ron de Bruin
http://www.rondebruin.nl



"bam" <(E-Mail Removed)> wrote in message news:(E-Mail Removed)...
> 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
>

Thanks very much for the answer and the link!

"Ron de Bruin" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>
> --
> Regards Ron de Bruin
> http://www.rondebruin.nl
>
>
>
> "bam" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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
>>
>
>