If row starts with X, delete 11 rows

G

gxm204

New Member
Joined
Nov 20, 2015
Messages
29
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
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
Sort by date Sort by votes
Try this:

Code: 
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
 
Upvote 0
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
1Title A
2Page X of Y
3Not this Page X of Y
4Page 1 of 2
5Not this Page X of Y
6
Sheet1


And, after the macro:[/n]


Excel 2007
A
1Title A
2Not this Page X of Y
3Not 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).

Code: 
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.
 
Upvote 0
I am working on cleaning a PDF file with a header
Click to expand...

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
1PDF File Header
2Page X of Y
31
42
53
64
75
86
97
108
119
1210
13Not this Page X of Y
141
152
163
174
185
196
207
218
229
2310
24Page 1 of 2
251
262
273
284
295
306
317
328
339
3410
35Not this Page X of Y
361
372
383
394
405
416
427
438
449
4510
46Page 7 of 11
471
482
493
504
515
526
537
548
559
5610
57
Sheet1


And, after the new macro:


Excel 2007
A
1PDF File Header
2Not this Page X of Y
31
42
53
64
75
86
97
108
119
1210
13Not this Page X of Y
141
152
163
174
185
196
207
218
229
2310
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).

Code: 
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.
 
Upvote 0
The user was happy with my script. And this script runs using a loop also.
hiker95 said:
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
PDF File Header
Page X of Y
Not this Page X of Y
Page 1 of 2
Not this Page X of Y
Page 7 of 11

<colgroup><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]5[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]6[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]7[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]8[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]9[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]10[/TD]

[TD="align: center"]13[/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]15[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]16[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]17[/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]18[/TD]
[TD="align: right"]5[/TD]

[TD="align: center"]19[/TD]
[TD="align: right"]6[/TD]

[TD="align: center"]20[/TD]
[TD="align: right"]7[/TD]

[TD="align: center"]21[/TD]
[TD="align: right"]8[/TD]

[TD="align: center"]22[/TD]
[TD="align: right"]9[/TD]

[TD="align: center"]23[/TD]
[TD="align: right"]10[/TD]

[TD="align: center"]24[/TD]

[TD="align: center"]25[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]26[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]27[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]28[/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]29[/TD]
[TD="align: right"]5[/TD]

[TD="align: center"]30[/TD]
[TD="align: right"]6[/TD]

[TD="align: center"]31[/TD]
[TD="align: right"]7[/TD]

[TD="align: center"]32[/TD]
[TD="align: right"]8[/TD]

[TD="align: center"]33[/TD]
[TD="align: right"]9[/TD]

[TD="align: center"]34[/TD]
[TD="align: right"]10[/TD]

[TD="align: center"]35[/TD]

[TD="align: center"]36[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]37[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]38[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]39[/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]40[/TD]
[TD="align: right"]5[/TD]

[TD="align: center"]41[/TD]
[TD="align: right"]6[/TD]

[TD="align: center"]42[/TD]
[TD="align: right"]7[/TD]

[TD="align: center"]43[/TD]
[TD="align: right"]8[/TD]

[TD="align: center"]44[/TD]
[TD="align: right"]9[/TD]

[TD="align: center"]45[/TD]
[TD="align: right"]10[/TD]

[TD="align: center"]46[/TD]

[TD="align: center"]47[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]48[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]49[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]50[/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]51[/TD]
[TD="align: right"]5[/TD]

[TD="align: center"]52[/TD]
[TD="align: right"]6[/TD]

[TD="align: center"]53[/TD]
[TD="align: right"]7[/TD]

[TD="align: center"]54[/TD]
[TD="align: right"]8[/TD]

[TD="align: center"]55[/TD]
[TD="align: right"]9[/TD]

[TD="align: center"]56[/TD]
[TD="align: right"]10[/TD]

[TD="align: center"]57[/TD]
[TD="align: right"][/TD]

</tbody>
Sheet1​



And, after the new macro:

Excel 2007
A
PDF File Header
Not this Page X of Y
Not this Page X of Y

<colgroup><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]5[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]6[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]7[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]8[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]9[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]10[/TD]

[TD="align: center"]13[/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]15[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]16[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]17[/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]18[/TD]
[TD="align: right"]5[/TD]

[TD="align: center"]19[/TD]
[TD="align: right"]6[/TD]

[TD="align: center"]20[/TD]
[TD="align: right"]7[/TD]

[TD="align: center"]21[/TD]
[TD="align: right"]8[/TD]

[TD="align: center"]22[/TD]
[TD="align: right"]9[/TD]

[TD="align: center"]23[/TD]
[TD="align: right"]10[/TD]

[TD="align: center"]24[/TD]
[TD="align: right"][/TD]

</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).

Code: 
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.
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Solved
Create a pdf with each page being just 4 rows and 1 column wide
Replies
3
Views
218
6StringJazzer
6StringJazzer
Keebler
  • Solved
clean code
5 6 7
Replies
66
Views
2K
Keebler
Keebler
S
  • Question Question
VBA code to determine InDesign Layout
Replies
6
Views
377
Akuini
Akuini
I
  • Question Question
Delete Rows based off criteria meeting
Replies
1
Views
416
ItalianPlatinum
I
L
  • Question Question
Prevent Pagebreak from hidden columns and rows, combine visible columns to one page wide
Replies
1
Views
228
bobsan42
bobsan42

Forum statistics

Threads
1,223,227
Messages
6,170,848
Members
452,361
Latest member
d3ad3y3

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top