Filter and Delete Rows

[pwill32]

Hi can anyone help?

I have posted this question at
https://www.mrexcel.com/forum/excel-...ml#post4858826

I have hundreds of rows of data with 11 columns A:K that I need to filter (sheet1).

I would like to first filter all the rows (A2:K) using the name in column A so all the rows for Sarah are showing and then filter all these rows using column E leaving only the rows (A2:K) that start with '0' (E:K)

Then repeat the process for David to leave only the rows (A2:K) that start with '1'(E:K)
Then again with Tom to leave only the rows (A2:K) that start with '-1'(E:K)
Then again with Bethany leaving rows starting with '2'(E:K)
And again with John leaving rows '-2'(E:K)
And again with Katie leaving rows '3'(E:K)

Deleting all the rows filtered out.

ie

Before







A
B
C
D
E
F
G
H
I
J
K
L
M
N




































1


Name

Date
Seq
Blank






















2


Sarah
26/05/17
1


1
1
-1
-2
-3
5
-6








3


David
26/05/17
1


1
1
-1
-2
-3
5
-6








4


Tom
26/05/17
1


-1
2
-2
-3
5
-6
-6








5


Bethany
26/05/17
1


2
-2
-3
5
6
-6
-6








6


John
26/05/17
1


-2
-3
4
5
6
-6
-6








7


Katie
26/05/17
1


-3
4
4
5
6
-6
-6








8


Sarah
26/05/17
2


0
-1
2
-2
-2
3
-3








9


David
26/05/17
2


-1
2
2
-2
-2
3
-3








10


Tom
26/05/17
2


-1
2
2
-2
-2
3
-3








11


Bethany
26/05/17
2


2
2
-2
-2
3
3
-3








12


John
26/05/17
2


-3
-3
-3
-3
6
7
-7








13


Katie
26/05/17
2


3
3
-3
4
5
-5
-9








14


Sarah
27/05/17
3


0
1
-1
2
2
-2
-3








15


David
27/05/17
3


1
2
2
2
-2
-3
-3








16


Tom
27/05/17
3


2
2
2
-2
-3
-3
-3








17


Bethany
27/05/17
3


-2
-3
4
5
6
-6
-6








18


John
27/05/17
3


-2
-3
-3
-3
-3
6
7








19


Katie
27/05/17
3


-3
-3
-3
-3
6
7
-7








20
































21


































After







A
B
C
D
E
F
G
H
I
J
K
L
M
N






Name

Date

Seq

Blank























1


Sarah
26/05/17
2


0
-1
2
-2
-2
3
-3








2


Tom
26/05/17
2


-1
2
2
-2
-2
3
-3








3


Bethany
26/05/17
2


2
2
-2
-2
3
3
-3








4


Katie
26/05/17
2


3
3
-3
4
5
-5
-9








5


Sarah
27/05/17
3


0
1
-1
2
2
-2
-3








6


David
27/05/17
3


1
2
2
2
-2
-3
-3








7


John
27/05/17
3


-2
-3
-3
-3
-3
6
7








8
































9
































10
































11
































12
































13
































14
































15
































16
































17
































18
































19
































20
































21


































I recorded a macro to do this mini version but as more names and data are added the code gets to large and I wondered if there would be a simpler way to do this?

Here is the Macro I recorded

Regards
pwill32

Sub FiltrA()

Range("A2:K2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.AutoFilter
ActiveSheet.Range("A2:K2").End(xlDown).AutoFilter Field:=1, Criteria1:="Sarah"
ActiveSheet.Range("A2:K2").End(xlDown).AutoFilter Field:=5, Criteria1:=Array( _
"1", "-1", "2", "-2", "3", "-3", "4", "-4", "5", "-5", "6", "-6", "7"
Range("A2:K2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.EntireRow.Delete
ActiveSheet.AutoFilterMode = False

Range("A2:K2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.AutoFilter
ActiveSheet.Range("A2:K2").End(xlDown).AutoFilter Field:=1, Criteria1:="David"
ActiveSheet.Range("A2:K2").End(xlDown).AutoFilter Field:=5, Criteria1:=Array( _
"0", "-1", "2", "-2", "3", "-3", "4", "-4", "5", "-5", "6", "-6", "7"
Range("A2:K2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.EntireRow.Delete
ActiveSheet.AutoFilterMode = False

Range("A2:K2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.AutoFilter
ActiveSheet.Range("A2:K2").End(xlDown).AutoFilter Field:=1, Criteria1:="Tom"
ActiveSheet.Range("A2:K2").End(xlDown).AutoFilter Field:=5, Criteria1:=Array( _
"0", "1", "2", "-2", "3", "-3", "4", "-4", "5", "-5", "6", "-6", "7"
Range("A2:K2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.EntireRow.Delete
ActiveSheet.AutoFilterMode = False

Range("A2:K2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.AutoFilter
ActiveSheet.Range("A2:K2").End(xlDown).AutoFilter Field:=1, Criteria1:="Bethany"
ActiveSheet.Range("A2:K2").End(xlDown).AutoFilter Field:=5, Criteria1:=Array( _
"0", "1", "-1", "-2", "3", "-3", "4", "-4", "5", "-5", "6", "-6", "7"
Range("A2:K2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.EntireRow.Delete
ActiveSheet.AutoFilterMode = False

Range("A2:K2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.AutoFilter
ActiveSheet.Range("A2:K2").End(xlDown).AutoFilter Field:=1, Criteria1:="John"
ActiveSheet.Range("A2:K2").End(xlDown).AutoFilter Field:=5, Criteria1:=Array( _
"0", "1", "-1", "2", "3", "-3", "4", "-4", "5", "-5", "6", "-6", "7"
Range("A2:K2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.EntireRow.Delete
ActiveSheet.AutoFilterMode = False

Range("A2:K2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.AutoFilter
ActiveSheet.Range("A2:K2").End(xlDown).AutoFilter Field:=1, Criteria1:="Katie"
ActiveSheet.Range("A2:K2").End(xlDown).AutoFilter Field:=5, Criteria1:=Array( _
"0", "1", "-1", "2", "-2", "-3", "4", "-4", "5", "-5", "6", "-6", "7"
Range("A2:K2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.EntireRow.Delete
ActiveSheet.AutoFilterMode = False


End Sub

[mdmackillop]

Create a table of data to preserve (Sheet2!A1). Use a helper column to identify items to be deleted.


Sarah
0


David
1


Tom
-1


Bethany
2


John
-2


Katie
3




Sub Test()
Dim Sh As Worksheet
Dim i, j, arr
Dim r As Range
Set Sh = Sheet1
lr = Sh.Cells(Rows.Count, 1).End(xlUp).Row
arr = Sheet2.Cells(1, 1).CurrentRegion
For j = 1 To UBound(arr)
For i = 2 To lr
If Sh.Cells(i, 1) = arr(j, 1) And Sh.Cells(i, 5) <> arr(j, 2) Then
Sh.Cells(i, 13) = "x"
End If
Next
Next
Sheet1.Columns(13).AutoFilter Field:=1, Criteria1:="x"
Sheet1.Columns(13).SpecialCells(xlCellTypeConstants).EntireRow.Delete
Sheet1.Columns(13).AutoFilter
End Sub

[pwill32]

Thanks mdmackillop,

I will give this a try when I get home from work tonight and let you know how I get on

Regards

pwill32

[mdmackillop]

No need to quote whole posts; only those lines relevant to your question

[YasserKhalil]

Cross-Post at this link
http://www.ozgrid.com/forum/showthread.php?t=204653

[pwill32]

Hi mdmackillop,

I just tried your code and it works as it should,

thank you for your help, much apprieciated

regards

pwill32