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

Have you tried the macro recorder from ToolsMacroRecord 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

On Tue, 19 Jun 2007 15:54:02 -0700, Alex.W
wrote:

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, 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),C OUNTA(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" wrote:

Have you tried the macro recorder from ToolsMacroRecord 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

On Tue, 19 Jun 2007 15:54:02 -0700, Alex.W
wrote:

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

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),C OUNTA(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),2 3)


Gord


On Tue, 19 Jun 2007 21:43:04 -0700, Alex.W
wrote:

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" wrote:

Have you tried the macro recorder from ToolsMacroRecord 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

On Tue, 19 Jun 2007 15:54:02 -0700, Alex.W
wrote:

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, 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" wrote:

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),C OUNTA(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),2 3)


Gord


On Tue, 19 Jun 2007 21:43:04 -0700, Alex.W
wrote:

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" wrote:

Have you tried the macro recorder from ToolsMacroRecord 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

On Tue, 19 Jun 2007 15:54:02 -0700, Alex.W
wrote:

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

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



On Wed, 20 Jun 2007 19:36:01 -0700, Alex.W
wrote:

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" wrote:

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),C OUNTA(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),2 3)


Gord


On Tue, 19 Jun 2007 21:43:04 -0700, Alex.W
wrote:

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" wrote:

Have you tried the macro recorder from ToolsMacroRecord 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

On Tue, 19 Jun 2007 15:54:02 -0700, Alex.W
wrote:

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
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" wrote:

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



On Wed, 20 Jun 2007 19:36:01 -0700, Alex.W
wrote:

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" wrote:

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),C OUNTA(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),2 3)


Gord


On Tue, 19 Jun 2007 21:43:04 -0700, Alex.W
wrote:

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" wrote:

Have you tried the macro recorder from ToolsMacroRecord 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

On Tue, 19 Jun 2007 15:54:02 -0700, Alex.W
wrote:

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

Good to hear you're off and running.

Gord

On Thu, 21 Jun 2007 16:31:00 -0700, Alex.W
wrote:

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" wrote:

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



On Wed, 20 Jun 2007 19:36:01 -0700, Alex.W
wrote:

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" wrote:

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),C OUNTA(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),2 3)


Gord


On Tue, 19 Jun 2007 21:43:04 -0700, Alex.W
wrote:

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" wrote:

Have you tried the macro recorder from ToolsMacroRecord 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

On Tue, 19 Jun 2007 15:54:02 -0700, Alex.W
wrote:

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