Filter Row of desire number + 1 row below it.

[motilulla]

Using Excel 2010
Hello,

There are mix numbers in the column E it is easy to filter any 1 number like if I want to select number 8, but I need bit more (if I select number 8 I want to be selected 1 row below it as well) does it is possible as shown in the example yellow highlighted row…selecting number 8 in the filter also filter 1 row below it as well.

Please advise how it could be done or suggest any VBA with input selection number.

MrExcel Question.xlsm
	A	B	C	D	E	F	G
1
2
3
4			Num	Temp	Filter 8+Next Row
5			Num	Temp	Filter 8+Next Row
6			38	70/71	0
7			39	70/71	8
8			40	70/71	5
9			1	71/72	9
10			2	71/72	8	<-----If I filter Num 8, I Need To Filter with it 1 Row below as well
11			3	71/72	8
12			4	71/72	5
13			9	71/72	7
14			10	71/72	7
15			11	71/72	6
16			12	71/72	8	<-----If I filter Num 8, I Need To Filter with it 1 Row below as well
17			13	71/72	7
18			14	71/72	10
19			15	71/72	9
20			16	71/72	6
21			17	71/72	5
22			18	71/72	7
23			19	71/72	7
24			20	71/72	7
25			21	71/72	8	<-----If I filter Num 8, I Need To Filter with it 1 Row below as well
26			22	71/72	8
27			23	71/72	5
28			24	71/72	6
29			25	71/72	6
30			26	71/72	6
31			27	71/72	7
32			28	71/72	6
33			29	71/72	8	<-----If I filter Num 8, I Need To Filter with it 1 Row below as well
34			30	71/72	12
35			31	71/72	9
36			32	71/72	7
37			33	71/72	5
38			34	71/72	9
39			35	71/72	10
40			36	71/72	4
41			37	71/72	6
42			38	71/72	8	<-----If I filter Num 8, I Need To Filter with it 1 Row below as well
43			1	72/73	7
44			2	72/73	6
45			3	72/73	7
46			4	72/73	8	<-----If I filter Num 8, I Need To Filter with it 1 Row below as well
47			5	72/73	7
48			6	72/73	8
49			7	72/73	7
50			8	72/73	5
51			9	72/73	6
52			10	72/73	8	<-----If I filter Num 8, I Need To Filter with it 1 Row below as well
53			11	72/73	5
54			12	72/73	8	<-----If I filter Num 8, I Need To Filter with it 1 Row below as well
55			13	72/73	6
56			14	72/73	9
57			15	72/73	8	<-----If I filter Num 8, I Need To Filter with it 1 Row below as well
58			16	72/73	8
59			17	72/73	6
60
61
62
63
Sheet12

Regards,
Moti

 

[MARK858]

Is there any reason why the rows in green aren't to be filtered?

Forum 27.3.xlsm
	C	D	E	F
4	Num	Temp	Filter 8+Next Row
5	Num	Temp	Filter 8+Next Row
6	38	70/71	0
7	39	70/71	8
8	40	70/71	5
9	1	71/72	9
10	2	71/72	8	<-----If I filter Num 8, I Need To Filter with it 1 Row below as well
11	3	71/72	8
12	4	71/72	5
13	9	71/72	7
14	10	71/72	7
15	11	71/72	6
16	12	71/72	8	<-----If I filter Num 8, I Need To Filter with it 1 Row below as well
17	13	71/72	7
18	14	71/72	10
19	15	71/72	9
20	16	71/72	6
21	17	71/72	5
22	18	71/72	7
23	19	71/72	7
24	20	71/72	7
25	21	71/72	8	<-----If I filter Num 8, I Need To Filter with it 1 Row below as well
26	22	71/72	8
27	23	71/72	5
28	24	71/72	6
29	25	71/72	6
30	26	71/72	6
31	27	71/72	7
32	28	71/72	6
33	29	71/72	8	<-----If I filter Num 8, I Need To Filter with it 1 Row below as well
34	30	71/72	12
35	31	71/72	9
36	32	71/72	7
37	33	71/72	5
38	34	71/72	9
39	35	71/72	10
40	36	71/72	4
41	37	71/72	6
42	38	71/72	8	<-----If I filter Num 8, I Need To Filter with it 1 Row below as well
43	1	72/73	7
44	2	72/73	6
45	3	72/73	7
46	4	72/73	8	<-----If I filter Num 8, I Need To Filter with it 1 Row below as well
47	5	72/73	7
48	6	72/73	8
49	7	72/73	7
50	8	72/73	5
51	9	72/73	6
52	10	72/73	8	<-----If I filter Num 8, I Need To Filter with it 1 Row below as well
53	11	72/73	5
54	12	72/73	8	<-----If I filter Num 8, I Need To Filter with it 1 Row below as well
55	13	72/73	6
56	14	72/73	9
57	15	72/73	8	<-----If I filter Num 8, I Need To Filter with it 1 Row below as well
58	16	72/73	8
59	17	72/73	6
Sheet3

 

[motilulla]

Is there any reason why the rows in green aren't to be filtered?

Hello MARK858, I am sorry it is my mistake, thank you for you remark yes these green also to filter you are right.

Kind Regards,
Moti

 

[MARK858]

I can't think of a way to autofilter the rows but you can hide the rows that don't meet the criteria with VBA, try...

Sub Moti()
    Dim i As Integer, MyNum As Integer
  
    Application.ScreenUpdating = False
  
    MyNum = Application.InputBox("Enter number", Type:=1)
  
    For i = Cells(Rows.Count, "D").End(xlUp).Row To 6 Step -1
        If Cells(i, "E").Value <> MyNum And Cells(i - 1, "E").Value <> MyNum Then _
                                   Cells(i, "E").EntireRow.Hidden = True
    Next
  
    Application.ScreenUpdating = True

End Sub

 

[motilulla]

I can't think of a way to filter the rows but you can hide the rows that don't meet the criteria with VBA, try...

Sub Moti()
    Dim i As Integer, MyNum As Integer
  
    Application.ScreenUpdating = False
  
    MyNum = Application.InputBox("Enter number", Type:=1)
  
    For i = Cells(Rows.Count, "D").End(xlUp).Row To 6 Step -1
        If Cells(i, "E").Value <> MyNum And Cells(i - 1, "E").Value <> MyNum Then _
                                   Cells(i, "E").EntireRow.Hidden = True
    Next
  
    Application.ScreenUpdating = True

End Sub

Hello MARK858, it is a big help mean time this worked super for what I wanted.
I leave it open may someone get filter idea.

I appreciate you help. Good Luck!

Kind Regards,
Moti

 

[motilulla]

Hello MARK858, it is a big help mean time this worked super for what I wanted.
I leave it open may someone get filter idea.

I appreciate you help. Good Luck!

Kind Regards,
Moti

Hello MARK858, I guess it has been 49 views after you gave a solution, no more replies this makes me believed there is no filter solution.

I am using your solution it is perfect I am happy to have this VBA. Request solved.

Have a nice day. Good Luck!

Regards,
Moti

 

[MARK858]

You're welcome