Printing populated rows only

I have an odd project that just crossed my desk. I'm looking for a 'better' solution than the one I found.

Situation: Research department has a large historical transaction document. There are several thousands of columns (over 14k). There are hundreds of rows. If every row were to be printed, it would span 2 or 3 pages in height (minimum). However, most of the rows are blank values. So out of 500 rows, maybe 15-25 are populated. Each transaction (column) has a random scattering of data that spans the 500 rows, but never more than 25 rows of data at a time.

The problem? No matter how we try to print this, there is far too much 'blank' space for each transaction. When they want to review a transaction, they may have to read 3 pages to find 15 rows of populated data. I would tell the system to skip blanks, but across all entries at least ONE of the 500 rows are populated somewhere. Frustratingly, every entry is different, making the print outs very difficult to read. (And this is not a finance dept, but a law firm. We need to make review of this material easier to study)

Ideally we'd like each transaction (each column) to print with the description column and ONLY the populated row for each transaction... which means each column must have different rows printing out.

I can easily filter the collection per column and force Excel to skip the blank rows, giving me a nice tight block of data. But I would have to do this manually for every column in the report.

In a pinch, I was able to do this. I used the filter feature to remove blanks, and then I printed the Column w/ the description (essentially selecting only two columns and manually printing the selection). I then 'hid' the column and repeated the process for each column in the report. The resulting print out was perfect, but very labor intensive. Luckily, they really only need 100 of the transactions.

For this one-time project, the manual option worked well. We got a page for each column with ONLY the populated rows printing out, and each column was given it's own page so that the reviewers could easily read the populated (only) rows for each transaction in hand.

However, this was a bit tedious, and I'm certain I was missing something pretty obvious. We don't use excel often at all, so I feel a bit lost on finding a better solution. I've been told we will be getting a very large historical report from several finance houses and we would need to do this again in the future.

In the ridiculously simplified table below... would there be a way to print each column (Transactions 1 thru 4) on it's own page WITH the description text, and only print the populated rows? (So that Transaction 1 would only print out LNAME and ZIP, #2 would print FNAME, ADDY, and ZIP, and so on)

	1	2	3	4
FName		James		Mary
LName	Smith		Jones
Addy		1234 Row St
City			New York
State				CA
Zip	11111
PH			555-555-5555
Cell		555-555-5555

Hi pbutche

The macro on the attched sheet works fine for the "ridiculously simplified table". Does it scale up correctly?

Having ensured that you have sufficient permissions to run macros,

Press Ctrl+SHIFT+Q

(Hint - the data is written to Sheet 2)

To view the code press Alt+F8 / "Step into"

Regards
Alastair

Thank you VERY much for this idea. I tested it on the noblanks file and it worked ideally - and I was hopeful it would solve our problems. However, when I grabbed a limited sampling of the real data (90 rows of 'descriptive labels, and 32 columns of populated data), when I tried to run the macro, I received an error: Too Many! You have exceeded the number of transactions for this column. Exiting system.

When I check the output on Sheet2; I can see the very first entry and 27 populated rows. This is a correct reflection of the first record's data. It appears to be starting on column 2 but doesn't manage to complete the transaction.

At this point, it wouldn't make sense without you seeing a real sample. So I've neutralized our data and attached a tiny sampling to see.

Again; thank you very much for your assistance.

Oh I'm sorry; a mis-communication. There are close to 90 rows... but almost all are blank with the exception of about 25 rows of DATA (at a time). So it isn't that there are only 25 rows, its just that of the 90 only 25 are actually populated at a time. And they change wildly as the report goes on, making it a challenge. I'm excited to try this solution! Thanks again.

Ah; close! I wish I could do what you are capable of doing. I would contribute to this in a more meaningful way. When I run the macro now (on the sample data), Record #1 is consolidated to 30 rows (instead of the 88). Record #2 however has all the 88 rows, leaving a large collection of empty rows. Same with the rest.

In this sample report, I reduced it to less than 100 rows just to keep it easy. However, the data population you see in this sample report is accurate. There are only 27 rows of populated data. So of the 88 rows, 27 have content, and 61 are blank. Here is the challenge. In the REAL report, the rows can go up to 500. In reality, I would have 461 blank rows (entire pages of almost nothing). And these values may change slightly; some records have 18 fields of data, other have 30 fields of data. 25-27 rows of data are the most common.

I hope that makes sense. And I greatly appreciate your efforts. Truly. Thank you very much.

Hi pbutche

Just goes to show that one should not try to squeeze in important macro-writing in between phone calls and eating lunch at the office!

Now I am away from that environment, I note that the seemingly empty cells are not empty at all, but are full of spaces. Thus, I now test to see if the cell is blank, or the first 2 characters are blanks.

Just in case you really did mean " on it's own page", I have included a routine that takes the new data on sheet 2 and puts a page break at the end of each (original) column's data. To run this, go on to Sheet 2 and press Ctrl+SHIFT+S

Let me know how you get on.

Regards
Alastair

Hi pbutche

Apparently if you click on the star at the bottom of the thread, it adds to my reputation. However I really don't take too much notice of this. I get my buzz from knowing that I have saved someone from a tedious job! (and I have had to do some tedious jobs in my time, and, like you, thought that there must be a better way of doing this! )

Regards
Alastair