SETTING PRINT AREA IN VBA

[Frankie]

I'm a beginner in Excel VBA. I have come across problem
to set the selected print area as follows :

ActiveSheet.PageSetup.PrintArea = "Range(Activecell,
Activecell.End(xlDown))"

It seems VBA only accepts absolute address :

ActiveSheet.PageSetup.PrintArea = "$B$4:$H$16"


Please give me some clues for setting a relative reference
print area instead of an abosolute one.

Thanks.


Rgds,
Frankie

 

[papou]

Hi Frankie
ActiveSheet.PageSetup.PrintArea = ActiveCell.Address(False, False) & ":" &
ActiveCell.End(xlDown).Address(False, False)

Cordially
Pascal

 

[Binzelli]

Frankie,

I assume you want to set the printarea from A1 to the last cell in th
sheet. The code below selects the last cel
(ActiveCell.SpecialCells(xlLastCell).Select) and assigns its rownumbe
to the variable Lastrow. (Lastrow = Selection.Row). Then the strin
"SelString" defining the range to be selected is made from tha
variable (SelString = "A1" & Lastrow). This should do the trick.


Code
-------------------

Sub SetPrintArea

Dim Lastrow As Double
Dim SelString As String

ActiveCell.SpecialCells(xlLastCell).Select
Lastrow = Selection.Row
SelString = "A1" & Lastrow

ActiveSheet.PageSetup.PrintArea = SelString

End Sub

 

[keepITcool]

do the trick? what trick?

your code makes 4 errors:

1: it will NEVER print more then 4 columns
2, it uses select where it needn't
3: it uses a lastrow variable which isn't needed either
4: it dims a row number as double????



to print the entire sheet just clear the Printarea:

ActiveSheet.PageSetup.PrintArea = ""





keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >

 

[Binzelli]

KeepITCool.... what's wrong with you mate ? Just trying to help
eachother here or not ?

Frankie asked > Please give me some clues for setting a relative reference

The code does just that. Your example just prints the entire sheet, not
quite what Frankie asked.

And for your 4 ERRORS:

1. It will indeed NEVER print more than 4 columns

Thats right, it's an example, remember ! Frankie can use whatever Range
(rows and columns) he likes. The code gives - as Frankie asked - a clue
for setting a relative reference

2, it uses select where it needn't

Sure ! Learning all the time ! Next time I'll use:

Code:
--------------------
Lastrow = ActiveCell.SpecialCells(xlLastCell).Row
--------------------

3: it uses a lastrow variable which isn't needed eitherSo what do you suggest ?

Code:
--------------------
ActiveSheet.PageSetup.PrintArea = "A1" & ActiveCell.SpecialCells(xlLastCell).Row
--------------------
more easy and nice and compact, but as Frankie said he was new to Excel
VBA, I thought the step-by-step approach makes things a bit more easy
to understand.

4: it dims a row number as double????

Should have been "Long"

Anyway, hope your next reaction is a bit more friendly.

Beetje collegialiteit kan geen kwaad Jurgen !

Groeten uit Amsterdam

 

[Frankie]

Dear Pascal,

Thanks for your help.

This is the solution that I needed. It is simple and
straightforward for a beginner like me.


Rgds,
Frankie

 

[papou]

Ok Frankie glad to help

Cordially
Pascal

"Frankie" <[email protected]> a écrit dans le message de
Dear Pascal,

Thanks for your help.

This is the solution that I needed. It is simple and
straightforward for a beginner like me.


Rgds,
Frankie