Hi, I've gotten tied up at work, so haven't been able to get back to writing a script for you.
Since the text that crops up first time in Range $A$1:$N$4 relyably repeats, (see, I'm
skipping the changing page nbr in the 15th (O) column), you could have your macro compare
that range of (4 rows x 14 columns) 56 cells, against the next set of 4 rows, and so forth.
Wherever there's a match, you tell the macro to delete those rows.
If you're not in the macro VBA realm yet, there's still a way:
Do you know about "Table" ranges? (If not, see the bottom of this note for instructions
on identifying a range as a table). You could identify the whole report as a Table range.
Then, using these three steps, you'll be able to hide the title rows out of sight by painting them blue and filtering
them out by color:
1. Filter the A column by checking off "USR code: " and "Tech".
a. Check by scrolling that these rows really are indeed only
from the repeating headers.
b. With the filter still on, highlight the whole range of them,
Paint them with, let's say Blue, for the background color.
c. Switch off filter to release it, to see all rows show again.
2. Filter the B column by checking off "Date: " and "Time".
a. Check by scrolling that these rows, too, are only from
the repeating header rows.
b. Same as above, highlight the rows with the filter still on,
and paint them Blue.
c. Switch off the filter to see all the rows. Look through again to
check that only the header rows have been colored blue.
d. If you want to keep the header on the very first rows - A1 to A4 - then
highlight that block of rows, and paint them back to "no color" via the
ribbon paint bucket.
3. Now, filter the table, but by color this time. Note the option in the middle of
the filter drop-down menu, the "Filter by Color" option.
a. Choose a column and using "Filter by Cell Color, select "no color"
NOW, you should only see the rows with content.
b. With the filter still on,
i. Copy-paste the whole report BY VALUE, to a new sheet (by hovering
ii. highlight a blank row of the original report, and copy it by column width
your mouse over "paste special" in the right-click the short-cut menu
- you'll see a 2nd floating mini-window appear, with an icon of a white
block and a horizontal double-headed arrow above it.
That one will only copy the WIDTH of the original columns to your new
worksheet. Repeat to copy over just the original report's formatting.
iii. Look over the new report to ensure it's what you are looking for,
and there you are, the report free of the repetitive header rows.
Alternately, you could just send your print (or copy to PDF) command to the printer
with the filters still on to keep the blue rows hidden, and it should work.
setting up a table range:
In case you have never used the "Table range" feature before, here's how to identify
the whole report as a "table range":
Click on the Insert ribbon, you'll see on the extreme left two Icons - the 2nd one
called Table. That's what you need. So, first highlight the whole report from A1,
across all the columns, and all the way down to the very last line of the report and
including its right-most column.
While that is highlighted, you click the Insert Ribbon's "Table" icon.
On the "Create Table pop up window, UNCHECK the "My table has headers" box -
so that Excel will add in a new row at the very top, with column1, column2, etc as
names for the columns to provide a filter button for every column within the range.
If you are hundred percent sure that only the header rows contain what you are
filtering by, this quick-and-dirty method should help you get that report out the door.
Have a nice weekend. Eve