I have a set of data that may change at any time, but I need to print it
without having to reset the Print Area manually every time. I've got a
CountIf(A:A, "<>") equation set to count how far down the Print Area needs to
go. But how can I enter it into the range reference for as a variable? Oh and
this is eventually going to be part of several macros for computer illiterate
to use.

You don't need macros. The print area is stored as a locally defined named
range. Go to the sheet that you want and select Insert -> Name -> Define ->
Select Print_Area

Change the absolute reference to a dynamic named range similar to this...

=offset(A1,0,0,counta("A:A"), counta("1:1"))

As elements are added to column A or row 1 the print area will grow (or
shrink)...
--
HTH...

Jim Thomlinson


"CorporateQAinTX" wrote:

> I have a set of data that may change at any time, but I need to print it
> without having to reset the Print Area manually every time. I've got a
> CountIf(A:A, "<>") equation set to count how far down the Print Area needs to
> go. But how can I enter it into the range reference for as a variable? Oh and
> this is eventually going to be part of several macros for computer illiterate
> to use.

Usually you try to avoid using worksheet functions in VBA because they are
less eifficient then VBA functions. But if you are targeting the code for
people who are familar with worksheet functions I guess it is OK.

Here are other methods of getting the Last row

Lastrow = CountIf(A:A, "<>")

If you have continuous data without any blank cells then use this

LastRow = Range("A1").end(xldown).row

If you have blank rows then you need to go to the end of the workbook row
65536 and move up until you find data. Because Excel 2007 has move than
65536 rows use Rows.Count which will work for either version of excel and get
the bottom of the worksheet.

LastRow = Range("A" & Rows.Count).end(xlup).Row

If you don't know which column contains the Last Row here is another way of
getting the LastRow


LastRow = Cells.SpecialCells(Type:=xlCellTypeLastCell).Row

Now Print the artea using the instruction below
---------------------------------------------------------------------
Range("A1:F" & LastRow.Printout


-------------------------------------------------

"CorporateQAinTX" wrote:

> I have a set of data that may change at any time, but I need to print it
> without having to reset the Print Area manually every time. I've got a
> CountIf(A:A, "<>") equation set to count how far down the Print Area needs to
> go. But how can I enter it into the range reference for as a variable? Oh and
> this is eventually going to be part of several macros for computer illiterate
> to use.

I like the way you're using the Naming system. Thanks. And btw, the macros
aren't for this. Just a very small part of the entire workbook. Thanks again.

"Jim Thomlinson" wrote:

> You don't need macros. The print area is stored as a locally defined named
> range. Go to the sheet that you want and select Insert -> Name -> Define ->
> Select Print_Area
>
> Change the absolute reference to a dynamic named range similar to this...
>
> =offset(A1,0,0,counta("A:A"), counta("1:1"))
>
> As elements are added to column A or row 1 the print area will grow (or
> shrink)...
> --
> HTH...
>
> Jim Thomlinson
>
>
> "CorporateQAinTX" wrote:
>
> > I have a set of data that may change at any time, but I need to print it
> > without having to reset the Print Area manually every time. I've got a
> > CountIf(A:A, "<>") equation set to count how far down the Print Area needs to
> > go. But how can I enter it into the range reference for as a variable? Oh and
> > this is eventually going to be part of several macros for computer illiterate
> > to use.

I don't normally use worksheet functions in VB. But since this is hopefully
something that will be used after I leave this job, I want to make sure that
my replacement will be able to understand it. Thanks for the help. It was
definately not a way I thought of doing it.

"Joel" wrote:

> Usually you try to avoid using worksheet functions in VBA because they are
> less eifficient then VBA functions. But if you are targeting the code for
> people who are familar with worksheet functions I guess it is OK.
>
> Here are other methods of getting the Last row
>
> Lastrow = CountIf(A:A, "<>")
>
> If you have continuous data without any blank cells then use this
>
> LastRow = Range("A1").end(xldown).row
>
> If you have blank rows then you need to go to the end of the workbook row
> 65536 and move up until you find data. Because Excel 2007 has move than
> 65536 rows use Rows.Count which will work for either version of excel and get
> the bottom of the worksheet.
>
> LastRow = Range("A" & Rows.Count).end(xlup).Row
>
> If you don't know which column contains the Last Row here is another way of
> getting the LastRow
>
>
> LastRow = Cells.SpecialCells(Type:=xlCellTypeLastCell).Row
>
> Now Print the artea using the instruction below
> ---------------------------------------------------------------------
> Range("A1:F" & LastRow.Printout
>
>
> -------------------------------------------------
>
> "CorporateQAinTX" wrote:
>
> > I have a set of data that may change at any time, but I need to print it
> > without having to reset the Print Area manually every time. I've got a
> > CountIf(A:A, "<>") equation set to count how far down the Print Area needs to
> > go. But how can I enter it into the range reference for as a variable? Oh and
> > this is eventually going to be part of several macros for computer illiterate
> > to use.

Thanks again guys. I just wanted to list what I ended up doing so others will
have at least one more good option available.

=CountIf(A:A,"<>")
I knew that my data would only go to the F column, so I put another formula
below that one.
="$A$1:$F$"&(CellAbove)

This gave me a range that changes based on the count. Then I set the Print
Area to equal the second cell in my macro. It works great now.


"CorporateQAinTX" wrote:

> I have a set of data that may change at any time, but I need to print it
> without having to reset the Print Area manually every time. I've got a
> CountIf(A:A, "<>") equation set to count how far down the Print Area needs to
> go. But how can I enter it into the range reference for as a variable? Oh and
> this is eventually going to be part of several macros for computer illiterate
> to use.