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