Set print range in VB macro

%3CLINGO-SUB%20id%3D%22lingo-sub-1565396%22%20slang%3D%22en-US%22%3ESet%20print%20range%20in%20VB%20macro%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1565396%22%20slang%3D%22en-US%22%3E%3CP%3EI%20need%20to%20set%20the%20print%20range%20within%20a%20VB%20for%20a%20table%20that%20has%20variable%20length%20from%20run%20to%20run.%20The%20table%20is%20made%20up%20of%20the%20sum%20of%20multiple%20subtotals%2C%20all%20of%20which%20change%20from%20run%20to%20tun.%20That%20only%20means%20that%20I%20don't%20know%20the%20last%20cell%20address%20until%20the%20table%20is%20assembles.%20How%20can%20I%20define%20the%20range%20for%20this%20variable%20size%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1565396%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1565469%22%20slang%3D%22de-DE%22%3ESubject%3A%20Set%20print%20range%20in%20VB%20macro%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1565469%22%20slang%3D%22de-DE%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F749160%22%20target%3D%22_blank%22%3E%40Ron_P88%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CDIV%20class%3D%22text-wrap%20tlid-copy-target%22%3E%3CDIV%20class%3D%22result-shield-container%20tlid-copy-target%22%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3EIn%20VBA%20a%20simple%20print%20preview%20to%20build%20yourself.%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22%22%3EIt%20can%20be%20set%20up%20as%20a%20module%2C%20as%20in%20the%20inserted%20workbook%2C%20or%20in%20any%20worksheet%20alone.%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FDIV%3E%3C%2FDIV%3E%3CDIV%20class%3D%22tlid-result-transliteration-container%20result-transliteration-container%20transliteration-container%22%3E%3CDIV%20class%3D%22tlid-transliteration-content%20transliteration-content%20full%22%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%20class%3D%22tlid-transliteration-content%20transliteration-content%20full%22%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%20class%3D%22tlid-transliteration-content%20transliteration-content%20full%22%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%20class%3D%22tlid-transliteration-content%20transliteration-content%20full%22%3E%3CP%3EIf%20you%20find%20this%20helpful%2C%20please%20mark%20it%20as%20%22Best%20Answer%22%20and%20as%20Like%20(click%20thumbs%20up)%2C%20it%20will%20be%20beneficial%20to%20more%20community%20members%20reading%20here.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENikolino%3C%2FP%3E%3CP%3EI%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FP%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1567650%22%20slang%3D%22en-US%22%3EBetreff%3A%20Set%20print%20range%20in%20VB%20macro%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1567650%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F722750%22%20target%3D%22_blank%22%3E%40Nikolino%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESorry%2C%20but%20I%20don't%20see%20how%20this%20helps%20me.%20Perhaps%20I%20wasn't%20clear%20enough%20in%20describing%20my%20goal%3F%20I%20want%20to%20program%20the%20Set%20Print%20Range%20into%20my%20macro%20so%20I%20can%20print%20a%20variable%20range%20without%20human%20intervention.%20So%20far%20I%20have%20not%20found%20any%20way%20to%20use%20a%20variable%20to%20set%20the%20lower%20bound%20of%20the%20print%20range.%20The%20example%20in%20your%20post%20appears%20to%20have%20a%20fixed%20address%20in%20the%20print%20range%20specification%20(A1%3AAH60)%20although%20thi9s%20doesn't%20appear%20to%20be%20the%20range%20displayed%20when%20the%20Print%20Preview%20button%20is%20selected.%20What%20am%20I%20missing%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1567697%22%20slang%3D%22de-DE%22%3ESubject%3A%20Set%20print%20range%20in%20VB%20macro%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1567697%22%20slang%3D%22de-DE%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F749160%22%20target%3D%22_blank%22%3E%40Ron_P88%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CDIV%20class%3D%22text-wrap%20tlid-copy-target%22%3E%3CDIV%20class%3D%22result-shield-container%20tlid-copy-target%22%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3ETailored%20to%20your%20area.%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%20%3CSPAN%3EIf%20other%20area%20is%20desired%20or%20the%20whole%20worksheet%20it%20can%20be%20changed%20by%20VBA.%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%20%3CSPAN%20class%3D%22%22%3EHope%20this%20worksheet%20meets%20your%20needs.%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22%22%3EIf%20not%20please%20give%20a%20short%20feedback.%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FDIV%3E%3C%2FDIV%3E%3CDIV%20class%3D%22tlid-result-transliteration-container%20result-transliteration-container%20transliteration-container%22%3E%3CDIV%20class%3D%22tlid-transliteration-content%20transliteration-content%20full%22%3E%26nbsp%3B%3C%2FDIV%3E%3C%2FDIV%3E%3CDIV%20class%3D%22result-footer%20source-or-target-footer%20tlid-copy-target%22%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%20class%3D%22result-footer%20source-or-target-footer%20tlid-copy-target%22%3ENikolino%3C%2FDIV%3E%3CDIV%20class%3D%22result-footer%20source-or-target-footer%20tlid-copy-target%22%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3EI%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1567742%22%20slang%3D%22en-US%22%3EBetreff%3A%20Set%20print%20range%20in%20VB%20macro%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1567742%22%20slang%3D%22en-US%22%3ESorry%20but%20when%20I%20execute%20your%20sub%20on%20my%20file%20before%20a%20print%20range%20is%20set%20I%20get%20a%204%20page%20display%20of%20the%20entire%20worksheet%20rather%20than%20the%20y2%3AAD312%20range%20that%20this%20week's%20data%20calls%20for.%20Another%20week%20with%20different%20data%20would%20call%20for%20a%20range%20of%20y2%20to%20some%20other%20lower%20bound.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1567767%22%20slang%3D%22de-DE%22%3ESubject%3A%20Set%20print%20range%20in%20VB%20macro%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1567767%22%20slang%3D%22de-DE%22%3EYou%20have%20to%20determine%20in%20advance%20which%20area%20you%20want%20to%20print%2C%20if%20the%20lines%20in%20this%20area%20are%20empty%20should%20not%20be%20printed%20or%20hidden%2C%20this%20means%20selecting%20a%20large%20printing%20area%20in%20advance%20depending%20on%20the%20data%20that%20can%20occur%20...%20is%20this%20correct%3F...%20What%20I%20have%20expressed.%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1567829%22%20slang%3D%22de-DE%22%3ESubject%3A%20Set%20print%20range%20in%20VB%20macro%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1567829%22%20slang%3D%22de-DE%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F749160%22%20target%3D%22_blank%22%3E%40Ron_P88%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3EMaybe%20this%20approach%20will%20help%20you%3C%2FSPAN%3E%3C%2FSPAN%3E.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1590645%22%20slang%3D%22en-US%22%3EBetreff%3A%20Set%20print%20range%20in%20VB%20macro%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1590645%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%20for%20your%20continued%20support%20of%20my%20problem.%20I%20apologize%20for%20not%20responding%20sooner%20but%20other%20unrelated%20events%20have%20taken%20up%20my%20time.%20Your%20latest%20example%20looks%20promising%20and%20I%20will%20have%20to%20work%20with%20it%20to%20fit%20my%20case.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EViele%20Dank!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERon%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

I need to set the print range within a VB for a table that has variable length from run to run. The table is made up of the sum of multiple subtotals, all of which change from run to tun. That only means that I don't know the last cell address until the table is assembles. How can I define the range for this variable size?

7 Replies

@Ron_P88 

In VBA a simple print preview to build yourself. It can be set up as a module, as in the inserted workbook, or in any worksheet alone.
 
 
 

If you find this helpful, please mark it as "Best Answer" and as Like (click thumbs up), it will be beneficial to more Community members reading here.

 

Nikolino

I know I don't know anything (Socrates)

@Nikolino 

 

Sorry, but I don't see how this helps me. Perhaps I wasn't clear enough in describing my goal? I want to program the Set Print Range into my macro so I can print a variable range without human intervention. So far I have not found any way to use a variable to set the lower bound of the print range. The example in your post appears to have a fixed address in the print range specification (A1:AH60) although thi9s doesn't appear to be the range displayed when the Print Preview button is selected. What am I missing?

@Ron_P88 

 

Tailored to your area.

If other area is desired or the whole worksheet it can be changed by VBA.

Hope this worksheet meets your needs. If not please give a short feedback.
 
Sorry but when I execute your sub on my file before a print range is set I get a 4 page display of the entire worksheet rather than the y2:AD312 range that this week's data calls for. Another week with different data would call for a range of y2 to some other lower bound.
You have to determine in advance which area you want to print, if the lines in this area are empty should not be printed or hidden, this means selecting a large printing area in advance depending on the data that can occur ... is this correct?... what I have expressed.

@Ron_P88 

 

Maybe this approach will help you.

Thank you for your continued support of my problem. I apologize for not responding sooner but other unrelated events have taken up my time. Your latest example looks promising and I will have to work with it to fit my case.

 

Viele Dank!

 

Ron