Help with Filter and Delete...... Please
 

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 someone please help. Thank
you in advance...

A B C D E F
A15862 Creation K5210 7118410 UPHOLSTERY EI-
A15862 Creation K5210 7118410 UPHOLSTERY E-M
A15862 Creation K5210 7139700 UPHOLSTERY EI-A15862 Creation
K5210 7139700 UPHOLSTERY FZ-A15862 Creation
L2
A15862 Creation TI-
A15862 Creation ZA-T-M-1
A15862 Creation


Les Stout

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

Help with Filter and Delete...... Please
 

Les

Apply a second filter to column A for whatever number you wish to
delete and then delete them.

When I do something like this, I usually set two range objects - one
for the autofilter range, and one for the data range (ie the same less
header row), and then use SpecialCells method to delete only the
visible cells. In xl2002 and 2003 you don't have to worry about using
the SpecialMethod, but it does make your code absolutely clear what you
are doing (and intending to do). Something like:

With rng
.Autofilter Field:=1, Criteria1:=<Your Number Here
rng2.SpecialCells(xlCellTypeVisible).EntireRow.Del ete
End With

Does this give you enough detail to work up a solution?

Best regards

Richard


Les Stout wrote:

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 someone please help. Thank
you in advance...

A B C D E F
A15862 Creation K5210 7118410 UPHOLSTERY EI-
A15862 Creation K5210 7118410 UPHOLSTERY E-M
A15862 Creation K5210 7139700 UPHOLSTERY EI-A15862 Creation
K5210 7139700 UPHOLSTERY FZ-A15862 Creation
L2
A15862 Creation TI-
A15862 Creation ZA-T-M-1
A15862 Creation


Les Stout

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

Help with Filter and Delete...... Please
 

Hi Richard,

Yes i think so thanks for your input...

Les Stout

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

Help with Filter and Delete...... Please
 

Hi, i have the following code which works great, i just do not know how
to delete all instances of the number in column "A". Should i copy and
save the filtered out numbers and then delete all instances using
vlookup ?

Sub FilterAndErase()
'
'
Application.ScreenUpdating = False
'
Rows("1:19").Delete Shift:=xlUp
Rows("1:1").AutoFilter

'************ Filter the field gAMS Created by ****************

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

End sub

Les Stout

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

Help with Filter and Delete...... Please
 

Are you wanting to delete rows with ("=*za-t-m-**") in them? Or keep this
row and delete all others with the same numer in A?

Mike F
"Les Stout" wrote in message
...
Hi, i have the following code which works great, i just do not know how
to delete all instances of the number in column "A". Should i copy and
save the filtered out numbers and then delete all instances using
vlookup ?

Sub FilterAndErase()
'
'
Application.ScreenUpdating = False
'
Rows("1:19").Delete Shift:=xlUp
Rows("1:1").AutoFilter

'************ Filter the field gAMS Created by ****************

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

End sub

Les Stout

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

Help with Filter and Delete...... Please
 

Hi Mike, I filter for ("=*za-t-m-**") and want to delete the row, which
i am managing with the code.. My problem is however that i have to
delete all of the rows with the same number for this row in column "A"
which is anything from 1 to 50 rows.



Les Stout

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

Help with Filter and Delete...... Please
 

Hi, i have the code below supplied by Tom Ogilvy. What i would like to
do is look up the range 2 and all instances in range 1 delete entire
row, then move one cell down in range 2 compare to range 1 & delete all
rows again etc...

Sub ABC()
Dim sh1 as worksheet, sh2 as worksheet
Dim rng1 as Range, rng2 as Range
Dim rw as Long, cell as Range
set sh1 = worksheets("Tabelle1")
set sh2 = worksheets("Temp")

rw = 2
set rng1 = sh1.Range(sh1.Cells(2,1),sh1.Cells(2,1).End(xldown ))
set rng2 = sh2.Range(sh2.Cells(2,1),sh2.Cells(2,1).End(xldown ))
for each cell in rng1
if application.countif(rng2,cell.value) = 0 then
cell.EntireRow.copy sh3.cells(rw,1)
rw = rw + 1
end if
Next

End sub

Les Stout

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