Excel: setting the print area in VB. Range A1:ActiveCell 1

[coolcarls]

I am just trying to have the print area to the last cell that has either data or any format or style or border etc. and back up to cell A1.
Any clues??
I tried: ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Activate
and then setting the range from cells(A1:activecell) but obviously that didn't work and is quite crude.
Thanks a lot!!
Carl

 

[cLFlaVA]

Take a look at the UsedRange property of the Worksheet object.

*cLFlaVA
----------------------------
A polar bear walks into a bar and says, "Can I have a ... beer?"
The bartender asks, "What's with the big pause?

 

[coolcarls]

so is it
ActiveSheet.UsedRange.Select
PageSetup.PrintArea = ActiveCell.CurrentRegion.Address
Sorry to be so lame!

 

[coolcarls]

Never mind.... I got it!!
ActiveSheet.UsedRange.Select
ActiveSheet.PageSetup.printarea = Selection

Duh!

Thanks again.

 

[cLFlaVA]

Well, to get the last used cell, I'd use

Sheets("SheetName").UsedRange

This will return $B$6 if that's your last used cell.
So,

ActiveSheet.PageSetup.PrintArea = "$A$1:" & ActiveSheet.UsedRange.AddressLocal

should do the trick

*cLFlaVA
----------------------------
A polar bear walks into a bar and says, "Can I have a ... beer?"
The bartender asks, "What's with the big pause?

 

[Rzrbkpk]

Can you narrow the usedrange property down to rows or columns? For example, column A and D have a borders on them but columns B and C will not always have data. Even though a large portion of the worksheet has border around cells, I do not want to print it all. Any suggestions?

 

[cLFlaVA]

Well, something you could do is just using the Rows.Count aspect of the UsedRange property. For example:

Say you have a complete used range from A1 to D50. Column D only has borders in it. You'd like to set the print range from A1 to B50. This is how you would do it:

ActiveSheet.PageSetup.PrintArea = "$A$1:B" & ActiveSheet.UsedRange.Rows.Count

*cLFlaVA
----------------------------
A pirate walks into a bar with a huge ship's steering wheel down his pants.
The bartender asks, "Are you aware that you have a steering wheel down your pants?"
The pirate replies, "Arrrrr! It's driving me nuts!

 

[Rzrbkpk]

I'm confused on the Rows.Count property. Can you explain how this functions. I want to print columns A-D, but only if columns B and C have data. Sorry to be a pain. Thanks.

 

[cLFlaVA]

UsedRange.Rows.Count returns the number of rows in the used range. Not what you're looking for.

There is no way I know to skip rows when printing, without simply hiding the rows or getting knee-deep in VBA coding.

*cLFlaVA
----------------------------
A pirate walks into a bar with a huge ship's steering wheel down his pants.
The bartender asks, "Are you aware that you have a steering wheel down your pants?"
The pirate replies, "Arrrrr! It's driving me nuts!

 

[Rzrbkpk]

FlaVA,
Thanks for the help. I was able to resolve my issue with the following conditional statement:

Private Sub Workboo_BeforePrint(Cancel As Boolean)
If IsEmpty(Cells(57,2)) Then
ActiveSheet.PageSetup.PrintArea = $A$19:$G$56
ElseIf Is Empty(Cells(95,2)) Then
ActiveSheet.PageSetup.PrintArea = $A$19:$G$56
Else
ActiveSheet.PageSetup.PrintArea = $A$19:$G$126
End If
End Sub

It works great.