• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Set a Dynamic Print Area based on count of rows

B

bjones

New Member
I have a sheet that I need to set the print area on based on a count of the rows (there is only one column to be printed)

My row count is in cell B1

The data to be printed is in A1:A(value of B1)

Can a Macro do this or VBA..?
 
Hi Bjones,


(a) Set the original print area something like this =Sheet1!$A$1:$A$21 by selecting 'Page Layout' / 'Print Area' / 'Set Print Area'


(b) Go to 'Formulas' / 'Name Manager' and then use a formula like this: =OFFSET(Sheet1!$A$1,0,0,Sheet1!$B$1,1)


Make sure that you change "Sheet1" to whatever your sheet name is.


The reference to cell B1 is the height or the number of rows to print.


If you would like to review the OFFSET function's parameters I am sure that Chandoo has some excellent blog posts about this!


Excel should now print only the required number of values


Cheers,

Kevin
 
You must log in or register to reply here.
Back
Top