Hi all,

Hope somebody can help me out!

I would like to run a macro to set a print area.

I need to print columns A thru S only.
I need to define the bottom row of the print area to be the last row
before column A becomes blank.

(The length of the list of data is dynamic, therefore I cannot use a
static range. I wish to omit any row below the point when column A
becomes blank, and I am unable to sort the data in anyway.)

I was thinking of using an IF statement inside a named range.

Any ideas?

Thanks in advance.

This is one way to name your print range:

Sub test()

Range("a1").Offset(Cells.SpecialCells(xlCellTypeLa stCell).Row + 1,
0).End(xlUp).Select
Range(Cells(1, 1), Cells(ActiveCell.Row, Range("s1").Column)).Name =
"rgPrint"

End Sub


"Dave Compton" wrote:

Hi all,

Hope somebody can help me out!

I would like to run a macro to set a print area.

I need to print columns A thru S only.
I need to define the bottom row of the print area to be the last row
before column A becomes blank.

(The length of the list of data is dynamic, therefore I cannot use a
static range. I wish to omit any row below the point when column A
becomes blank, and I am unable to sort the data in anyway.)

I was thinking of using an IF statement inside a named range.

Any ideas?

Thanks in advance.

Insert Name =Define

for Sheet1!Print_Area use

=Offset(Sheet1!$A$1,0,0,CountA(Sheet1!$A:$A),19)


If you go into page setup, the formula can be destroyed.

--
Regards,
Tom Ogilvy


"Dave Compton" wrote in message
ups.com...
Hi all,

Hope somebody can help me out!

I would like to run a macro to set a print area.

I need to print columns A thru S only.
I need to define the bottom row of the print area to be the last row
before column A becomes blank.

(The length of the list of data is dynamic, therefore I cannot use a
static range. I wish to omit any row below the point when column A
becomes blank, and I am unable to sort the data in anyway.)

I was thinking of using an IF statement inside a named range.

Any ideas?

Thanks in advance.

=Offset(Sheet1!$A$1,0,0,CountA(Sheet1!$A:$A),19)

Am I right that requires column A to have no holes in the data? ... to be a
required field?

(Perhaps that is the case)

But, if there were an entry in A1, A2, and A4, it would still report 3, is
what I mean.

Right?

Yep.

But you can work around it:

Insert|Name|Define
Names in workbook: Sheet1!LastRow
Use this formula
Refers to: =LOOKUP(2,1/(Sheet1!$A$1:$A$2000<""),ROW(Sheet1!$A$1:$A$2000) )

(Make that 2000 big enough to extend past the last possible row--but don't use
the whole column.)

Then once mo
Insert|Name|Define
Names in workbook: Sheet1!Print_Area
Use this formula
Refers to: =OFFSET(Sheet1!$A$1,0,0,lastRow,5)

That last 5 represents the last column to print (A:E).

And change the worksheet (sheet1) if necessary (in all the places).

mark wrote:

=Offset(Sheet1!$A$1,0,0,CountA(Sheet1!$A:$A),19)

Am I right that requires column A to have no holes in the data? ... to be a
required field?

(Perhaps that is the case)

But, if there were an entry in A1, A2, and A4, it would still report 3, is
what I mean.

Right?

--

Dave Peterson

Wow! I tried all of them and they all worked! Now I have to choose
one.......tough call.

Thanks for the replies guys. Really appreciated. This helped complete
an important project which should help secure my promotion!