Define a dynamic Print_Area

[John]

I've been searching on the internet and have found just enough tidbits
to get me on the road to frustration

I would like to set up a dyn. Print_Area from A1 to column K(n).
I tried:
1. =INDIRECT("A1:K"&COUNTIF(UK!A:A,">0"))
2. =OFFSET(UK!$A$1,0,0,Countif(UK!$A:$A,"<>"&0),11)

The number seems the one that works better but not as I expected.
Where am I wrong?
Thanks for any help
Regards
John

 

[Claus Busch]

Hi John,

Am Sun, 13 Jan 2013 11:32:15 +0100 schrieb John:

1. =INDIRECT("A1:K"&COUNTIF(UK!A:A,">0"))
2. =OFFSET(UK!$A$1,0,0,Countif(UK!$A:$A,"<>"&0),11)

try:
=OFFSET(UK!$A$1,,,COUNTA(UK!$A:$A),11)


Regards
Claus Busch

 

[Claus Busch]

Hi John,

Am Sun, 13 Jan 2013 11:42:24 +0100 schrieb Claus Busch:

try:
=OFFSET(UK!$A$1,,,COUNTA(UK!$A:$A),11)

create this range on the worksheet and give it the name myRange.
Then you can use the name for your printarea:
With ActiveSheet.PageSetup
.PrintArea = "myRange"
End With

Or you do it in VBA competely:

Sub Test()
Dim LRow As Long
Dim LCAddr As String

With ActiveSheet
LRow = .Cells(.Rows.Count, 1).End(xlUp).Row
LCAddr = .Cells(LRow, 11).Address
With .PageSetup
.PrintArea = "$A$1:" & LCAddr
End With
End With
End Sub


Regards
Claus Busch

 

[John]

Hallo Claus,
thanks for your suggestions and help.
I'll use the VBA solutions.

Thanks again.
John.