Delete Entire Rows
 

I have the following data in a spreadsheet:

IAN 3025364 3127372008000
FONDA 3025390 3125601110000
FRED 3025390 3125601110000
FRED 3025398 3005703448000
IAN 3025801 3025805491000
IAN 3025801 5805492000000
IAN 3025801 6312140000000
IAN 3025801 5878415000000
IAN 3025801 5878415000000
FRED 5346161 3127382840000
FRED 5346161 3127382284000
IAN 5394701 3127372421000

Is there a way in code to have it delete the rows where
the first set of numbers starts with 302 so the final list
would look like:

FRED 5346161 3127382840000
FRED 5346161 3127382284000
IAN 5394701 3127372421000

It will be looping through worksheets in the workbook and
there are alot of sheets so would like a way to run a
macro to do it. Any help would be appreciated. And
thanks to all those that have been helping me learn
programming in Excel.

Wally Steadman

Delete Entire Rows
 

Believe EdgeOfCity meant:

LastRow=range("b65536").end(xlup).row
for i=lastrow to 1 Step -1
if left(cells(i,2),3)="302" then
rows(i).delete
endif
next i


--
Regards,
Tom Ogilvy



EdgeOfCity wrote in message
...
LastRow=range("b65536").end(xlup).row
for i=lastrow to 1
if left(cells(i,2),3)="302" then
rows(i).delete
endif
next i
"Wally Steadman" дÈëÓʼþ
...
I have the following data in a spreadsheet:

IAN 3025364 3127372008000
FONDA 3025390 3125601110000
FRED 3025390 3125601110000
FRED 3025398 3005703448000
IAN 3025801 3025805491000
IAN 3025801 5805492000000
IAN 3025801 6312140000000
IAN 3025801 5878415000000
IAN 3025801 5878415000000
FRED 5346161 3127382840000
FRED 5346161 3127382284000
IAN 5394701 3127372421000

Is there a way in code to have it delete the rows where
the first set of numbers starts with 302 so the final list
would look like:

FRED 5346161 3127382840000
FRED 5346161 3127382284000
IAN 5394701 3127372421000

It will be looping through worksheets in the workbook and
there are alot of sheets so would like a way to run a
macro to do it. Any help would be appreciated. And
thanks to all those that have been helping me learn
programming in Excel.

Wally Steadman

Delete Entire Rows
 

Wally,
If all the sheets are alike and have data only in columnA to C with a header
row (row1) you can take advantage of the Advanced filter.

Sub Macro4()
Application.ScreenUpdating = False
For i = 1 To Worksheets.Count
Worksheets(i).Activate
LR = Range("A" & Rows.Count).End(xlUp).Row
Range("F3").Formula = "=LEFT(B2,3)<""302"""
With Range("A1:C" & LR)
..Select
..AdvancedFilter _
Action:=xlFilterCopy, _
CriteriaRange:=Range("F2:F3"), _
CopyToRange:=Range("H1:J1"), Unique:=False
End With
Range("A1").Select
Next
Application.ScreenUpdating = True
End Sub

by the way dose anybody know the criteria for Auto filter custom to do the
same job.

Cecil

"Wally Steadman" wrote in message
...
I have the following data in a spreadsheet:

IAN 3025364 3127372008000
FONDA 3025390 3125601110000
FRED 3025390 3125601110000
FRED 3025398 3005703448000
IAN 3025801 3025805491000
IAN 3025801 5805492000000
IAN 3025801 6312140000000
IAN 3025801 5878415000000
IAN 3025801 5878415000000
FRED 5346161 3127382840000
FRED 5346161 3127382284000
IAN 5394701 3127372421000

Is there a way in code to have it delete the rows where
the first set of numbers starts with 302 so the final list
would look like:

FRED 5346161 3127382840000
FRED 5346161 3127382284000
IAN 5394701 3127372421000

It will be looping through worksheets in the workbook and
there are alot of sheets so would like a way to run a
macro to do it. Any help would be appreciated. And
thanks to all those that have been helping me learn
programming in Excel.

Wally Steadman