Solved

Excel macro to print selection

Posted on 2016-09-29
17
48 Views
Last Modified: 2016-09-30
Hi Experts,
I have a program that hides rows and columns to show just what info I need.
I now need to print this filtered data.
I would like to Select the the data table then prompt to ask how many copies do you want printed.
Is there a way to force the printer properties to "print selection" AND BEST FIT TO SHEET?? Hmmmmmm

This sample image shows Column L and Column M as being the last two columns to include in the range to print, but, the table will grow and more columns will be added as I add more trainings. So the solution should be easy for me to change the LAST two grey columns later. The last row to include in the selection will always be the last name in the list.
 04.JPGThanks so much.
OPL-MATRIX.xlsm
0
Comment
Question by:chris pike
  • 9
  • 8
17 Comments
 
LVL 31

Expert Comment

by:Rob Henson
Comment Utility
Default Print Area uses the Used Area which in your case would include the extra columns. By setting a manual Print Area it ignores the Used Area setting. Therefore you can create a Dynamic Range which counts the number of columns and rows, then use that as the Print Area.

Set the Page Setup to Best Fit and it will stay like that unless it gets changed.
0
 

Author Comment

by:chris pike
Comment Utility
Hi Rob
This range will always change everytime we select a new column to show.
I need a code that will know where the last column is and the last row to select and print.
0
 
LVL 31

Expert Comment

by:Rob Henson
Comment Utility
I can't download your file to implement in your file but just done so in a file of my own.

I have assumed report sheet is called OPL and then used this formula to create a Named Range "DynamicPrint":

=OFFSET(OPL!$A$1,0,0,COUNTA(OPL!$A:$A),COUNTA(OPL!$1:$1))

You may have to adjust the result of the COUNTA bits to allow for blank columns/rows in the header area; looks like A5 is blank so that would be +1 to rows (first COUNTA) and looks like columns B & C are blank so would be +2 to columns (second COUNTA).

Manually set the Print Area to something to create the Print_Area name in the Name Manager. Select the Print_Area in the Name Manager and the refers to box at the bottom will show the manual range you set. Change that to =DynamicPrint  (or whatever you decide to call it).
0
 

Author Comment

by:chris pike
Comment Utility
In the example above the last column is M and the last row is 28.

L and M are my Date columns I want to show up beside the selected row to view,.

L and M will get bumped further down when I add more and more columns (training names)
0
 
LVL 31

Expert Comment

by:Rob Henson
Comment Utility
For the rows it will effectively count all rows but because they are hidden they won't appear in the Print.

For the screen print shown, the last populated row I assume is 56 so the Dynamic Print Area will go down to row 56 but because rows 29 to 56 are hidden they won't show on the print.

Are the headers in columns beyond column M populated or blank? If they are blank then the count of columns will stop at column L anyway.
0
 

Author Comment

by:chris pike
Comment Utility
Here is a better picture. I hide the filter buttons on the table. If you can't open it I can email it to you or something to help.

I deleted the empty row, but the columns I can not, too tricky.

Not sure how to start that formula, I guess we need to change as I deleted one row.
07.JPG
0
 

Author Comment

by:chris pike
Comment Utility
Yes after Column M will be blank...
And yes right now row 56 would be the last row.....
0
 
LVL 31

Accepted Solution

by:
Rob Henson earned 500 total points
Comment Utility
OK, assuming sheet name OPL still, the formula would be:

=OFFSET(OPL!$A$1,0,0,COUNTA(OPL!$A:$A)+r,COUNTA(OPL!$1:$1)+c)   adjust the values of r & c as below.

From what I can see:

r will need to be 1, A7 is blank
c will need to be at least 2, columns B & C are blank; can't see columns E to K.

Alternative to adjusting the formula would be to insert just an apostrophe in those cells in column A and row 1 which are "blank".
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 31

Expert Comment

by:Rob Henson
Comment Utility
With a bit more playing just found an alternative, only slight difference.

Rather than creating a Dynamic Range and then using that Range name as the definition for the Print Area, just use the OFFSET formula to define the Print_Area.

Create the Print_Area manually and then in the name manager replace the fixed range for Print_Area with the formula.
0
 

Author Comment

by:chris pike
Comment Utility
The print area changes constantly.
Sometimes there are 3 rows sometime 50 rows.
0
 
LVL 31

Expert Comment

by:Rob Henson
Comment Utility
Yes, I understand that. So long as the last row in column A (hidden or otherwise) marks the bottom of the Print Range the printed sheet will be correct; only the visible rows will show on the print.

You would now be down to determining the "Best Fit". I would suggest setting the page layout to "Fit all columns on one sheet" and then use the Headers in Row 8 as a Repeat Titles Row.
0
 

Author Comment

by:chris pike
Comment Utility
Thanks Rob,
I need your help...
 What is the new formula that I should use? I am a bit confused?
Thanks so much for your patience.

And How do I run the formula.?
Thanks Rob... It may be better if I wait until you have access to download?
0
 
LVL 31

Expert Comment

by:Rob Henson
Comment Utility
Select the area manually. On the Page Layout tab use the Print Area button to set that as the Print Area.

Then on the Formula tab go to Name Manager. There will be named Range for Print_Area for that sheet. In the refers to box at the bottom of the window it will show a fixed range, the range you selected.

Replace that fixed range with the offset formula above, with values for r & c. At the left hand end of that input box click the green tick to confirm. At the other end of the input box there is a Range Selector button, if you click that the range that the formula determines will be shown with "Marching Ants" dotted lines. If the area shown is not correct then you will need to adjust the values of r & c as mentioned earlier. Or just fill those cells in row 1 (columns B C and maybe E to K) with an apostrophe, and the same for those cells in column A that you know should be blank, A7 in earlier example.

If still not clear, can you just copy that single sheet to another file and save it, but not as xlsm as that is what I can't download.
0
 

Author Comment

by:chris pike
Comment Utility
ok GREAT,
I got it to work, I had to put -8 for the r value , couldn't see the marching ants at first , but i figured it out.

So if my table changes and I add more columns or rows, I just fiddle with those 2 numbers correct?
.And of course first change the print area.
0
 

Author Closing Comment

by:chris pike
Comment Utility
Awesome , makes it look so simple.
0
 
LVL 31

Expert Comment

by:Rob Henson
Comment Utility
Once you have the r & c values set, you shouldn't have to adjust them; the range will adjust then based on columns and rows used.

The thought behind the r & c was to allow for blank cells in the top left area. Probably better to just insert an apostrophe as suggested before then you don't need to adjust those numbers.

Once the Print Area formula is in you won't need to change the Print Area, it will adjust automatically. The one issue I did notice while playing was that if you went into the Page Setup dialogue where you set the margins etc and go to the Sheet tab, this shows the current range rather than the formula and clicking OK on that dialogue overwrites the formula in the Name Manager with a fixed range. So, do all the page setting; margins, titles etc, with a fixed range and then once it is all setup as you want it use the formula to define the Print Area.
0
 

Author Comment

by:chris pike
Comment Utility
Ahhh ok .
I will be careful to set it up in that order, gotcha.

Well I have a hidden sheet with instructions how to set up the print Area again if it gets messed up.
Thanks for your help.

Also found a script that unhides all columns and rows so it resets the sheet again after the print is made.
Works great.

Thanks Roy, Nice work.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now