Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Excel macro to print selection

Posted on 2016-09-29
17
Medium Priority
?
119 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 8
17 Comments
 
LVL 33

Expert Comment

by:Rob Henson
ID: 41821751
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
ID: 41821798
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 33

Expert Comment

by:Rob Henson
ID: 41821799
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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 

Author Comment

by:chris pike
ID: 41821806
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 33

Expert Comment

by:Rob Henson
ID: 41821817
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
ID: 41821833
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
ID: 41821837
Yes after Column M will be blank...
And yes right now row 56 would be the last row.....
0
 
LVL 33

Accepted Solution

by:
Rob Henson earned 2000 total points
ID: 41821864
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
 
LVL 33

Expert Comment

by:Rob Henson
ID: 41821886
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
ID: 41821890
The print area changes constantly.
Sometimes there are 3 rows sometime 50 rows.
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 41821909
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
ID: 41821930
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 33

Expert Comment

by:Rob Henson
ID: 41821946
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
ID: 41822079
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
ID: 41822460
Awesome , makes it look so simple.
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 41823064
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
ID: 41823461
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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
There are times when I have encountered the need to decompress a response from a PHP request. This is how it's done, but you must have control of the request and you can set the Accept-Encoding header.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

636 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