Solved

Excel macro to print selection

Posted on 2016-09-29
17
70 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 32

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 32

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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

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 32

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 32

Accepted Solution

by:
Rob Henson earned 500 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 32

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 32

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 32

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 32

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Outlook Free & Paid Tools
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

777 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