Using names ranges to set print area in a macro

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(xlCellTypeLastCell).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" <[email protected]> wrote in message
news:[email protected]...
> 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 more:
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!