Need Help Deleting Raw Data Page Breaks

bowler1517

New Member
Joined
Nov 30, 2016
Messages
2
I am struggling to find a way to create a formula or macro to delete some Raw data that appears in a report I run.
If anyone can help me I would greatly appreciate it.

Throughout the document the report I run creates a header for every page break in excel columns that I need to delete easily without manually having to find the data and delete it.

Here is the data I need to remove and it is the same data throughout the document except the page number changes.


Date: 11/30/2016 Page: 1 Completed Work Report
Time: 8:28:28 AM Version: 2.0.11.4
USR code: 62 SpeedConnect Michigan
Tech WT Acct # Name Address Received By/When Completed Work Units Lag Comments
Init/Final RC
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
Also, are you also creating the report in Excel directly off that raw data file, or is another program reading your raw data in Excel to generate the report, ie reading from Excel in order to create a report in Word?
 
Upvote 0
The report I am creating is in excel. I use another program at my company to run a report and it creates an excel spreadsheet with the information. I will be using this information to create another report in excel but need the raw data to be cleaned up before I can do this.

The data I need deleted is on multiple rows on the report I get from my companies program.
What the program does is takes a multiple page report and prints to file in an excel format.
So where there would be a new page on the report it has this header.
Looks like this in multiple rows throughout.
Data.png
[/URL]image uploading[/IMG]
 
Upvote 0
potentially, the search for tech in A, then delete that and the three rows above, then re-search for Tech and repeat, little fiddly, but quick in comparison I think
 
Last edited:
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,040
Members
448,543
Latest member
MartinLarkin

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