Set an undefined (variable) print area through a Macro

K

Kate

Does anyone know how to set up a macro that will
automatically set the print area to a changing number of
rows?
For example, if one list has 20 rows of data and the other
list has 100 rows available,I'm looking for it to set the
print area for both lists using the same macro.
 
T

Tushar Mehta

Interesting idea. The following formula, tested with Print Preview,
sets the range on a worksheet by worksheet basis. Adjust to your
specific requirement.

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

Note the use of addresses without the sheet name but with a leading !

The specific formula above is actually not very useful, but it
illustrates the key principle involved.

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Need Help with Macro Security 1
VBA word change keys 0
Footer On Last Page Only Not Working 1
Need VBA Macro to Loop through pages and print counter in footer of each page 9
How to automatically set print area in Excel template? 6
function on the print area 1
Problem with Upload Center on Office 2010 when using encrypted area on disk 0
Need help modifying code 0

Top