Issues trying to dynamically establish the print range

Hi Experts,

I'm having several issues trying to create a dynamic print range for Sheet1 which has 8 side-by-side(contiguous) Tables.  While I am encountering several issues in the process, they may be related but if at the end of the day, it turns out that I need to add additional points (via "pointer questions") because I've raised more than one issue, I'd be happy to do that - I just don't want to have to break this up into separate issues because I don't know if these issues are directly related or not - I can only say that they are all frustrating my purpose here.

I'd like the Print Area to be restricted to the visible columns in the selected Table (there are 8 macro buttons in Column A that display the 8 Tables while hiding all of the other columns not in the given Table.  While the Sheet has formulas and conditional formatting down to Row 515, I only want to print those rows whose formulas return data. Also, Column A should not be printed with any of the Tables.  I'm not married to the method I am using (and am willing to go a different direction) but I am still interested in what is wrong with this approach.

These are the issues I am seeing:

1) I've tried to use the advice at: http://stackoverflow.com/questions/5324537/excel-macro-dynamically-set-print-area,

... and have set up a dynamic range ("MyNamedRange") in Excel with the following formula:

=OFFSET(Sheet1!$B$15, 0, 0,COUNTA(Sheet1!B15:B515),SUBTOTAL(103,Sheet1!15:15))

But, after running the macros to select the Table and viewing the Print Preview, and then examining the Range "MyNamedRange" (note there are also Worksheet change events code that runs, too but it should be unrelated to this print area setting macro), I see the formula changed to:

=OFFSET(Sheet1!$B$15, 0, 0,COUNTA(Sheet1!XEZ17:XEZ517),SUBTOTAL(103,Sheet1!17:17))

I don't know why - I tried it first as a Workbook level named range, but it is presently set at a Worksheet level range, but neither seems to make any difference to the anomalies I am seeing here.  Why is this happening?

2) The SUBTOTAL formula with the 103 parameter is supposed to ignore hidden values but with only 7 visible columns (in it's present state (after invoking the View_Client macro (with the Add/Delete Client button), this portion of the formula is returning a count of 33 (and checking the count of items in row 15 after clicking the "View All Columns" button, I find that there are 33 items with data on row 15). BTW, I'm testing that by using the formula in Cell G1 and selecting just the Subtotal function and pressing F9 to Calculate just that component but then escaping out so the calculated result doesn't replace the formula (I do realize that the formula in a worksheet doesn't work the same as it does in a dynamically named range (thus the #VALUE error in G1), so I am just doing that for testing purposes.  Why am I getting the wrong result with this, or maybe more specifically, why are the Hidden columns being counted?

3) When I first started running this code (after manually creating the dynamic "MyNamedRange"), the code ran without error.  Now, I am getting a Run-time error 1004 "Reference is not valid" on the 2nd line of code below.  Somehow (and I don't know how), after getting this error repeatedly, the macro intermittently was working, but now it keeps on stopping with that error.  Why is this happening?

Sub SetMyPrintArea()

Sheets(1).Activate
ActiveSheet.PageSetup.PrintArea = "MyNamedRange"

End Sub

Open in new window


4) After clicking the Add/Delete Client, Arrivals or Departure buttons and looking at Print Preview, the resulting display is (somehow, without running the "SetMyPrintArea" macro) exactly the range I want.  However, if I click on the Car Groups, Avis Porsche Matrix, Room Requirements, or Special Request buttons, the resulting Print Preview is showing a blank page.  Since all of these macros do the same thing with the only difference being which columns are hidden and which are displayed (based upon a key word in Row 600), I don't know why some of them come up blank  (Edit:  There is one difference in the View_Room_Requirements_V1" macro where the line of code:

     ThisWorkbook.Sheets(1).Range("4:13").EntireRow.Hidden = False

- it is True for the other 7 macros, and False in this one as I want those rows displayed only in the related Table).  What explains these blank pages and how can I fix it?

Sorry for the long post, but these are all issues I'm encountering in trying to get this to work.

Hope someone can assist here!

Jeff
EE-Example.xlsm
LVL 2
Jeffrey SmithOwnerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rob HensonFinance AnalystCommented:
I have used the OFFSET function on numerous occasions and also had some issues.

I have seen the changes in cell references where I have formulated the required formula in a cell and then copied into the Name Manager, for some reason it sometimes changes the relative references; I have got into the habit of making sure that all row/column references are absolute.

I normally use a COUNTA for the columns and rows figures. Downside to COUNTA, and I guess SUBTOTAL as well, is that it will ignore blank cells, so if you have gaps between your tables (as you would have) they will be ignored. If your tables are fixed width and a known number of columns between, you can set the column figure to a fixed number.

Alternatively, you could create yourself a Named Range for the last column of the known total width, or next column might be easier, so that as columns are inserted/deleted the position of this cell moves. You can then refer to that cell with COLUMN function to get the number of that column, assuming you are starting in column A will be the same number of columns required.

Thanks
Rob H
Jeffrey SmithOwnerAuthor Commented:
Hi Rob and thanks for posting.

I'm pretty ticked off right now because I just lost my entire response to your comments while I was Previewing my response and somehow clicked something that made my entire question page disappear (along with my response) and it defaulted back to the http://www.experts-exchange.com page.  The SAME thing happened when I was constructing and reviewing my original (long) post and I had to redo that, too.  This might be something for EE's programmers to look at.

Anyway, I believe I now have it working.  

These are the things I did:

I deleted the Print_Area range which somehow had been defined as =Sheet1!$B$15:$AH$21, and then ran the "View_PORSCHE_MATRIX_V1" macro and checked the Print_Area range and found it was now defined as Sheet1!$B$15:$DP$21  (Column DP is the last Column in the last Table and Row 21 was the last one with data so that seems right).  AND, running the macro came back with the correct Columns selected and visible on the Print Preview instead of the blank page (yea !).

I then changed the SetMyPrintArea macro to this:

Sub SetMyPrintArea()

Dim sht As Worksheet
Set sht = ThisWorkbook.Worksheets(1)

sht.Activate
sht.PageSetup.PrintArea = "MyNamedRange"

End Sub

Open in new window


... which initially seemed to not throw the early run-time error AND allows only the rows with data to be printed (double yea !!)  Subsequentially, though (after running several times without any run-time errors), it sometimes fails again on the statement:  sht.PageSetup.PrintArea = "MyNamedRange" ... with the same Run-time error 1004 "Reference is not valid".  Why does this happen intermittently?  And strangely enough, if I comment that line out, I still seem to get the results I want even though that statement is not executed (so, I'll accept the result but I don't know why it's working).  

So, I think I have solved most of my own problems here although some credit might be due to using partially Absolute References in the dynamic named range (I made the Rows Absolute but I don't think the Columns should be Absolute if it is going to be able to refer to the different Columns in the different Tables as a truly dynamic print facility.  My dynamic named range formula is now:

=OFFSET(Sheet1!$B$15, 0, 0,COUNTA(Sheet1!B$15:B$515),SUBTOTAL(103,Sheet1!$3:$3))

and things seem to be working even though I see the Columns in the dynamic range name formula still change (and maybe that's supposed to happen under these circumstances).  

So, I'm willing to award some points for the Absolute Reference advice but I would still like to find out why that macro is intermittently throwing that run-time error.  Any thoughts on this?  

I'm attaching my latest file.

Jeff
EE-Example-2.xlsm

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jeffrey SmithOwnerAuthor Commented:
Question was apparently abandoned by the Expert and EE. I found most of the answers by myself as I had no follow-up from anyone else.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.