Selecting all cells that contain a value AND the cell below it

[ExcelEndeavor]

I am working with a list of our offices and some of them still show a fax number. I went to "Find and Select" to show all of the cells with the word "Fax" in them. How do I also select the corresponding fax numbers in the cell below them so I can delete all at once?

Thank you in advance.

 

[alansidman]

Give us a sample worksheet using XL2BB (No pictures please). We can then write a program that is specific to your needs. 8-10 records that are representative of your actual file with dummy information.

 

[ExcelEndeavor]

Give us a sample worksheet using XL2BB (No pictures please). We can then write a program that is specific to your needs. 8-10 records that are representative of your actual file with dummy information.

It is a simple sheet - everything is in Column A because I haven't sorted it out yet. I just want to get rid of the fax numbers and then I can start parsing.

Test.xlsx
	A
1	Office 1
2	123 Main St
3	This City, IA, 12345
4	phone
5	123-456-7890
6	Office 2
7	123 Main St
8	This City, IA, 12345
9	phone
10	123-456-7890
11	fax
12	123-456-7890
13	Office 3
14	123 Main St
15	This City, IA, 12345
16	phone
17	123-456-7890
18	fax
19	123-456-7890
20	Office 4
21	123 Main St
22	This City, IA, 12345
23	phone
24	123-456-7890
25	Office 5
26	123 Main St
27	This City, IA, 12345
28	phone
29	123-456-7890
30	fax
31	123-456-7890
32	Office 6
33	123 Main St
34	This City, IA, 12345
35	phone
36	123-456-7890
37	Office 7
38	123 Main St
39	This City, IA, 12345
40	phone
41	123-456-7890
42	fax
43	123-456-7890
44	Office 8
45	123 Main St
46	This City, IA, 12345
47	phone
48	123-456-7890
49	fax
50	123-456-7890
51	Office 9
52	123 Main St
53	This City, IA, 12345
54	phone
55	123-456-7890
56	Office 10
57	123 Main St
58	This City, IA, 12345
59	phone
60	123-456-7890
61	fax
62	123-456-7890
Sheet1

 

[alansidman]

Option Explicit

Sub Fax()
    Dim lr As Long, i As Long
    lr = Range("A" & Rows.Count).End(xlUp).Row
    For i = lr To 1 Step -1
        If Range("A" & i) = "fax" Then
            Range("A" & i + 1).Delete
            Range("A" & i).Delete
        End If
    Next i
    MsgBox "completed"
End Sub