Print Macro

[Guest]

I am looking for a macro that lets me print a spreadsheet with a set number
of columns but changing number of rows. Headings for repeating are rows 1 to
3. I also need the macro to control the Page Setup criteria.

Alex.W

 

[Gord Dibben]

Have you tried the macro recorder from Tools>Macro>Record New Macro for the Page
Setup?

The rows variable could be handled by using a Dynamic Range for the print araea.

For Dynamic Range info see Debra Dalgleish's site.

http://www.contextures.on.ca/xlNames01.html#Dynamic


Gord Dibben MS Excel MVP

 

[Guest]

Gord, thanks for the reference.

I entered the formula as suggested on Debra's site but it only partly works.
=OFFSET(Register!$D$4,0,0,COUNTA(Register!$D:$D),COUNTA(Register!$1$1))

The area in my spreadsheet that I want to print starts at D4 and goes to
column Z, it currently has 260 rows of data (the rows vary over time, the
columns do not).

The formula above gives me columns D to G to Row 1500. Have I entered the
formula correctly?

Alex.W

 

[Gord Dibben]

If you have entered as posted, Excel should throw an error message

You have missed a colon. Should be

=OFFSET(Register!$D$4,0,0,COUNTA(Register!$D:$D),COUNTA(Register!$1:$1))

I tested and had no problem with the range expanding/contracting with
additions/deletions. The formula ignores blank columns so that should not be an
issue

Do you have anything in rows 261:1500 that Excel would consider as data?

For the column issue, since you have a fixed number, maybe try

=OFFSET(Register!$D$4,0,0,COUNTA(Register!$D:$D),23)


Gord

 

[Guest]

Gord, thanks once again. All worked except it still wants to select down to
row 1500. The cells below row 260 are empty. Some are formatted though for
date type, decimal point and conditional formatting. Is this the problem?
Alex.W

 

[Gord Dibben]

Alex

Formatted but blank cells should not cause a problem.

A rogue space in a cell could however.

Select A1 then CTRL + End.

Where does Excel take you?

You may have to delete all rows below 260 or start at 1500 and work your way up
because Excel thinks your data extends that far.

Save, close and re-open.


Gord

 

[Guest]

Gord
Have done as you suggested. All is working perfectly. Many thanks for your
time and patience with me. Have a great day.
Alex.W

 

[Gord Dibben]

Good to hear you're off and running.

Gord

Gord
Have done as you suggested. All is working perfectly. Many thanks for your
time and patience with me. Have a great day.
Alex.W