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

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

Cordially
Pascal

"Frankie" a écrit dans le message de
...
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

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:D" & 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:D" & Lastrow

ActiveSheet.PageSetup.PrintArea = SelString

End Sub

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


Good luc

--
Message posted from http://www.ExcelForum.com

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 wrote:

Frankie,

I assume you want to set the printarea from A1 to the last cell in the
sheet. The code below selects the last cell
(ActiveCell.SpecialCells(xlLastCell).Select) and assigns its rownumber
to the variable Lastrow. (Lastrow = Selection.Row). Then the string
"SelString" defining the range to be selected is made from that
variable (SelString = "A1:D" & 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:D" & Lastrow

ActiveSheet.PageSetup.PrintArea = SelString

End Sub

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


Good luck


---
Message posted from http://www.ExcelForum.com/

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:D" & 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


---
Message posted from http://www.ExcelForum.com/

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

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

Cordially
Pascal

"Frankie" a écrit
dans le message de
...
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


.

Ok Frankie glad to help

Cordially
Pascal

"Frankie" 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

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

Cordially
Pascal

"Frankie" a écrit
dans le message de
...
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


.