Help needed with find & Delete
 

Hi all, i have many rows with the same number in column A and i am
filtering column F for the criteria "ZA-T-M-*" which is working fine.
However i need somehow to delete all rows with the same number in column
A. The second last row has ZA-T-M-* in it so all rows with instances of
"A15862" in column "A" muist be deleted. Can you please help with some
code please. Thank you in advance...

A B C D E F
A15862 Creation K5210 7118410 UPHOLSTERY Donner/EI-
A15862 Creation K5210 7118410 UPHOLSTERY Jakob/L2A
A15862 Creation K5210 7139700 UPHOLSTERY Wilhelm/EI-A15862 Creation
K5210 7139700 UPHOLSTERY Gutowski/FZ-A15862 Creation Weber/L2-U
A15862 Creation Archut/TI-
A15862 Creation ZA-T-M-1
A15862 Creation




Les Stout

*** Sent via Developersdex http://www.developersdex.com ***

Help needed with find & Delete
 

Further to my last post is the code that i am using...
Dim Rng As Range
Dim i As Long
Dim myArr As Variant
Dim myRows As Long
myRows = Cells(Application.Rows.Count, 1).End(xlUp).Row
myArr = Array("=*za-t-m-**")
For i = LBound(myArr) To UBound(myArr)

ActiveSheet.Range("A1:P" & myRows).AutoFilter Field:=8,
Criteria1:=myArr(i)
With ActiveSheet.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
Next i
ActiveSheet.AutoFilterMode = False

Les Stout

*** Sent via Developersdex http://www.developersdex.com ***

Help needed with find & Delete
 

Change myArr = Array("*za-t-m-**") to myArr = Array("<*za-t-m-**"). Your
range to delete is refering to Visible Cells, so make what you want to get
rid of visible in the AutoFilter.


Dim Rng As Range
Dim i As Long
Dim myArr As Variant
Dim myRows As Long
myRows = Cells(Application.Rows.Count, 1).End(xlUp).Row
myArr = Array("<*za-t-m-**")
For i = LBound(myArr) To UBound(myArr)

ActiveSheet.Range("A1:P" & myRows).AutoFilter Field:=6, Criteria1:=myArr(i)
With ActiveSheet.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
Next i
ActiveSheet.AutoFilterMode = False

Mike F
"Les Stout" wrote in message
...
Further to my last post is the code that i am using...
Dim Rng As Range
Dim i As Long
Dim myArr As Variant
Dim myRows As Long
myRows = Cells(Application.Rows.Count, 1).End(xlUp).Row
myArr = Array("=*za-t-m-**")
For i = LBound(myArr) To UBound(myArr)

ActiveSheet.Range("A1:P" & myRows).AutoFilter Field:=8,
Criteria1:=myArr(i)
With ActiveSheet.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
Next i
ActiveSheet.AutoFilterMode = False

Les Stout

*** Sent via Developersdex http://www.developersdex.com ***