If row starts with X, delete 11 rows

[gxm204]

Hello, I am working on cleaning a PDF file with a header that I don't want to include in my text analysis. Each page begins with the phrase "Page X of Y," followed by 10 more lines of header information.

The program I envision would have VBA find each cell that starts with the word "Page."

Then delete that row plus the following 10 rows (total of 11 rows).

This sounds like a good use of VBA but I am too new to write it myself! Thank you all very much.

Best,
George

 

[My Aswer Is This]

What column would this word "Page" be in ?

 

[gxm204]

First column. Thank you!

 

[My Aswer Is This]

Try this:

Sub Delete_Page()
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
    For i = Lastrow To 1 Step -1
        If Left(Cells(i, 1).Value, 4) = "Page" Then
            Rows(i).Resize(11).Delete
        End If
    Next
Application.ScreenUpdating = True
End Sub

 

[gxm204]

Beautiful. That is perfect. Thank you so very much. A good incentive learn more VBA!

 

[My Aswer Is This]

Glad I was able to help you. Come back here to Mr. Excel next time you need additional assistance.

Beautiful. That is perfect. Thank you so very much. A good incentive learn more VBA!

 

[hiker95]

gxm204,

Here is another macro for you to consider that does not do any looping thru the rows in column A, and, should be very fast.

Sample raw data:

Excel 2007
	A
1	Title A
2	Page X of Y
3	Not this Page X of Y
4	Page 1 of 2
5	Not this Page X of Y
6
Sheet1

And, after the macro:[/n]

Excel 2007
	A
1	Title A
2	Not this Page X of Y
3	Not this Page X of Y
4
5
6
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Sub DeleteRowsBeginningWith_Page()
' hiker95, 12/18/2016, ME981049
Application.ScreenUpdating = False
Columns("A").Replace "Page*", "#N/A", xlWhole
On Error Resume Next
Columns("A").SpecialCells(xlConstants, xlErrors).EntireRow.Delete
On Error GoTo 0
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the DeleteRowsBeginningWith_Page macro.

 

[hiker95]

Each page begins with the phrase "Page X of Y," followed by 10 more lines of header information.

gxm204,

I missed that part.

Be back in a little while.

 

[hiker95]

I am working on cleaning a PDF file with a header

I assume that the first row in your worksheet is a PDF File Header.

Here is another macro solution for you to consider that will not loop in each of the rows in the raw data.

Sample raw data:

Excel 2007
	A
1	PDF File Header
2	Page X of Y
3	1
4	2
5	3
6	4
7	5
8	6
9	7
10	8
11	9
12	10
13	Not this Page X of Y
14	1
15	2
16	3
17	4
18	5
19	6
20	7
21	8
22	9
23	10
24	Page 1 of 2
25	1
26	2
27	3
28	4
29	5
30	6
31	7
32	8
33	9
34	10
35	Not this Page X of Y
36	1
37	2
38	3
39	4
40	5
41	6
42	7
43	8
44	9
45	10
46	Page 7 of 11
47	1
48	2
49	3
50	4
51	5
52	6
53	7
54	8
55	9
56	10
57
Sheet1

And, after the new macro:

Excel 2007
	A
1	PDF File Header
2	Not this Page X of Y
3	1
4	2
5	3
6	4
7	5
8	6
9	7
10	8
11	9
12	10
13	Not this Page X of Y
14	1
15	2
16	3
17	4
18	5
19	6
20	7
21	8
22	9
23	10
24
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Sub DeleteRowsBeginningWith_Page_V2()
' hiker95, 12/18/2016, ME981049
Dim lr As Long, n As Long, i As Long, p As Range, rng As Range
Application.ScreenUpdating = False
n = Application.CountIf(Columns(1), "Page*")
If n > 0 Then
  lr = Cells(Rows.Count, 1).End(xlUp).Row
  Set rng = Range("A1:A" & lr)
  For i = 1 To n Step 1
    Set p = rng.Find("Page*", LookAt:=xlWhole)
    If Not p Is Nothing Then
      Rows(p.Row).Resize(11).Delete
    End If
  Next i
End If
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the DeleteRowsBeginningWith_Page_V2 macro.

 

[My Aswer Is This]

The user was happy with my script. And this script runs using a loop also.

I assume that the first row in your worksheet is a PDF File Header.

Here is another macro solution for you to consider that will not loop in each of the rows in the raw data.

Sample raw data:

Excel 2007

	A
1	PDF File Header
2	Page X of Y
3	1
4	2
5	3
6	4
7	5
8	6
9	7
10	8
11	9
12	10
13	Not this Page X of Y
14	1
15	2
16	3
17	4
18	5
19	6
20	7
21	8
22	9
23	10
24	Page 1 of 2
25	1
26	2
27	3
28	4
29	5
30	6
31	7
32	8
33	9
34	10
35	Not this Page X of Y
36	1
37	2
38	3
39	4
40	5
41	6
42	7
43	8
44	9
45	10
46	Page 7 of 11
47	1
48	2
49	3
50	4
51	5
52	6
53	7
54	8
55	9
56	10
57

<colgroup><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

And, after the new macro:

Excel 2007

	A
1	PDF File Header
2	Not this Page X of Y
3	1
4	2
5	3
6	4
7	5
8	6
9	7
10	8
11	9
12	10
13	Not this Page X of Y
14	1
15	2
16	3
17	4
18	5
19	6
20	7
21	8
22	9
23	10
24

<colgroup><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Sub DeleteRowsBeginningWith_Page_V2()
' hiker95, 12/18/2016, ME981049
Dim lr As Long, n As Long, i As Long, p As Range, rng As Range
Application.ScreenUpdating = False
n = Application.CountIf(Columns(1), "Page*")
If n > 0 Then
  lr = Cells(Rows.Count, 1).End(xlUp).Row
  Set rng = Range("A1:A" & lr)
  For i = 1 To n Step 1
    Set p = rng.Find("Page*", LookAt:=xlWhole)
    If Not p Is Nothing Then
      Rows(p.Row).Resize(11).Delete
    End If
  Next i
End If
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the DeleteRowsBeginningWith_Page_V2 macro.