pwill32
07-02-2017, 03:58 PM
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
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