I need help filtering out blank cells for printing! (VBA)

**dpleventhal** · 10-31-2011, 06:30 PM

Hi.
I have a spreadsheet with about 15 tabs. Of these 15 only three will be visible to users & they will need the ability to print them as a packet (i.e. all three at one time). My plan has been to create a simple a "print packet" button & connect it to a macro/vba. But I'm running into print problems.

Two of the tabs (labeled "2 - Doc Prep Documents" & "3 - Closing & Funding Docs") contain formulas that either return data from other worksheets or return a blank (" ").

Example:
> In worksheet "2 - Doc Prep Documents", cell G23 the formula is: =IF('Data Validation Lists - DP'!D5="","",'Data Validation Lists - DP'!D5)
> In worksheet "2 - Doc Prep Documents", cell D23 the formula is: =IF(G23="","",CHAR(163))

Excel keeps wanting to print ALL the cells that contain formulas, even if there is no value in them. I've been trying to write something that will:
1) Filter out the blanks on the two aforementioned tabs
2) Print all three pages
3) Then turn off the auto-filter (so that all the rows are displayed).

I don't want to print the blank cells (even if there is a formula in them).

The columns I need to print are columns A:K and the rows, as I mentioned, will vary based on other user selections.

The three tab names are:
"1 - Request" -- nothing fancy here...this one is set to go.
"2 - Doc Prep Documents" - this is one that I'm stuck on
"3 - Closing & Funding Docs" - this is one that I'm stuck on

Can anyone help a brotha out?

Thanks,
David

**davesexcel** · 10-31-2011, 08:50 PM

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

**dpleventhal** · 10-31-2011, 11:08 PM

Hi Dave,
Thanks for your reply. I've attached a dummy spreadsheet.

The idea is that the user goes to tab labeled "2 - Doc Prep Documents" & selects from the five drop downs. The items they select drive the list that populates below. Some choices will net a smaller list...other choices will net a longer list.

The items the user selects in tab "2 - Doc Prep Documents" are also copied via a formula to tab labeled "3 - Closing & Funding Documents"...and a different list is populated there as well.

Some times you might be printing 3 pages of stuff...other times it might be 12 pages - all depends on which items the user selects. Right now Excel is wanting to print ALL pages where the formula is present - which on tab 2 is to row 189...and on tab 3 is to row 188.

What I'm trying to get is some help creating a print job that will print:
1) Tab labeled "1 - Request"
2) Tab labeled "2 - Doc Prep Request" --- Only the rows where data is actually populated
3) Tab labeled "3 - Closing & Funding Docs" --- Only the rows where data is actually populated

Let me know if this helps clarify what I'm trying to accomplish. I really appreciate your help!

- David