Delete Entire Rows

  • Thread starter Thread starter Wally Steadman
  • Start date Start date
W

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
 
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
 
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
 
sorry .
thank you for finding this error.
Tom Ogilvy said:
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
Click to expand...
 
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
 
Back
Top