Setting Print Area, includes text, numbers, charts & graphs
 

Well, it seems to have worked. It's too bad that the method of hiding
unused rows & columns didnt work (it misses objects/charts, etc),
because that REALLY cut down on wasted space & page count.

Oh well, ya cant have everything.

Thanks for the help though!

~arawn


Dave Peterson wrote in message ...
What happens if you just reset the last cell, then reset the print range (not
specifying the address at all)?

Option Explicit
Sub testme01()

'Dim strLCell As String

With ActiveSheet
.UsedRange
'reset last cell
'strLCell = .Cells.SpecialCells(xlCellTypeLastCell).Address
'get last cell address
.PageSetup.PrintArea = ""
'set the print area
End With

End Sub

Arawn wrote:

I need to set print areas on Excel sheets automatically. I will never
know what a sheet will contain, but I do need to be able to set the
print areas.

I have tried a variety of methods, but have not had the desired
results:

With ActiveSheet
.UsedRange
'reset last cell
strLCell = .Cells.SpecialCells(xlCellTypeLastCell).Address
'get last cell address
.PageSetup.PrintArea = "$A$1:" & strLCell
'set the print area
End With

This method doesn't recognize charts/graphs/objects apparently.

I also tried hiding unused columns/rows (as described on J-Walk.com)
but that didnt seem to work either.

Any help would be appreciated.

Setting Print Area, includes text, numbers, charts & graphs
 

Arawm,

One way to capture the charts (if they are embedded on the worksheet) is to
go to the cell under the lower right corner of the chart and enter a space
(using the spacebar). Now Excel will see that cell as part of your used
area and include it in
strLCell = .Cells.SpecialCells(xlCellTypeLastCell).Address

--
sb
"Arawn" wrote in message
om...
Well, it seems to have worked. It's too bad that the method of hiding
unused rows & columns didnt work (it misses objects/charts, etc),
because that REALLY cut down on wasted space & page count.

Oh well, ya cant have everything.

Thanks for the help though!

~arawn


Dave Peterson wrote in message
...
What happens if you just reset the last cell, then reset the print range
(not
specifying the address at all)?

Option Explicit
Sub testme01()

'Dim strLCell As String

With ActiveSheet
.UsedRange
'reset last cell
'strLCell = .Cells.SpecialCells(xlCellTypeLastCell).Address
'get last cell address
.PageSetup.PrintArea = ""
'set the print area
End With

End Sub

Arawn wrote:

I need to set print areas on Excel sheets automatically. I will never
know what a sheet will contain, but I do need to be able to set the
print areas.

I have tried a variety of methods, but have not had the desired
results:

With ActiveSheet
.UsedRange
'reset last cell
strLCell = .Cells.SpecialCells(xlCellTypeLastCell).Address
'get last cell address
.PageSetup.PrintArea = "$A$1:" & strLCell
'set the print area
End With

This method doesn't recognize charts/graphs/objects apparently.

I also tried hiding unused columns/rows (as described on J-Walk.com)
but that didnt seem to work either.

Any help would be appreciated.

Setting Print Area, includes text, numbers, charts & graphs
 

Is there a way to do this automatically from within a macro? I am
opening hundreds of worksheets in sequence, but I never know what is
going to be in them.

Any assistance would be appreciated.

~arawn

"steve" wrote in message ...
Arawm,

One way to capture the charts (if they are embedded on the worksheet) is to
go to the cell under the lower right corner of the chart and enter a space
(using the spacebar). Now Excel will see that cell as part of your used
area and include it in
strLCell = .Cells.SpecialCells(xlCellTypeLastCell).Address

--
sb
"Arawn" wrote in message
om...
Well, it seems to have worked. It's too bad that the method of hiding
unused rows & columns didnt work (it misses objects/charts, etc),
because that REALLY cut down on wasted space & page count.

Oh well, ya cant have everything.

Thanks for the help though!

~arawn


Dave Peterson wrote in message
...
What happens if you just reset the last cell, then reset the print range
(not
specifying the address at all)?

Option Explicit
Sub testme01()

'Dim strLCell As String

With ActiveSheet
.UsedRange
'reset last cell
'strLCell = .Cells.SpecialCells(xlCellTypeLastCell).Address
'get last cell address
.PageSetup.PrintArea = ""
'set the print area
End With

End Sub

Arawn wrote:

I need to set print areas on Excel sheets automatically. I will never
know what a sheet will contain, but I do need to be able to set the
print areas.

I have tried a variety of methods, but have not had the desired
results:

With ActiveSheet
.UsedRange
'reset last cell
strLCell = .Cells.SpecialCells(xlCellTypeLastCell).Address
'get last cell address
.PageSetup.PrintArea = "$A$1:" & strLCell
'set the print area
End With

This method doesn't recognize charts/graphs/objects apparently.

I also tried hiding unused columns/rows (as described on J-Walk.com)
but that didnt seem to work either.

Any help would be appreciated.

Setting Print Area, includes text, numbers, charts & graphs
 

I don't like putting spaces into empty cells. But I have used formulas like
="".

It looks empty, but isn't. And I hope that when I look at that cell, I'll
remember why I did it.

Option Explicit
Sub testme()

Dim myShape As Shape
Dim wks As Worksheet

For Each wks In ActiveWorkbook.Worksheets
With wks
For Each myShape In .Shapes
With myShape
If IsEmpty(.BottomRightCell) Then
.BottomRightCell.Formula = "="""""
End If
End With
Next myShape
End With
Next wks

End Sub

You could look for the bottom-most and right-most, but why not just plop those
formulas whenever the cell is empty?

Arawn wrote:

Is there a way to do this automatically from within a macro? I am
opening hundreds of worksheets in sequence, but I never know what is
going to be in them.

Any assistance would be appreciated.

~arawn

"steve" wrote in message ...
Arawm,

One way to capture the charts (if they are embedded on the worksheet) is to
go to the cell under the lower right corner of the chart and enter a space
(using the spacebar). Now Excel will see that cell as part of your used
area and include it in
strLCell = .Cells.SpecialCells(xlCellTypeLastCell).Address

--
sb
"Arawn" wrote in message
om...
Well, it seems to have worked. It's too bad that the method of hiding
unused rows & columns didnt work (it misses objects/charts, etc),
because that REALLY cut down on wasted space & page count.

Oh well, ya cant have everything.

Thanks for the help though!

~arawn


Dave Peterson wrote in message
...
What happens if you just reset the last cell, then reset the print range
(not
specifying the address at all)?

Option Explicit
Sub testme01()

'Dim strLCell As String

With ActiveSheet
.UsedRange
'reset last cell
'strLCell = .Cells.SpecialCells(xlCellTypeLastCell).Address
'get last cell address
.PageSetup.PrintArea = ""
'set the print area
End With

End Sub

Arawn wrote:

I need to set print areas on Excel sheets automatically. I will never
know what a sheet will contain, but I do need to be able to set the
print areas.

I have tried a variety of methods, but have not had the desired
results:

With ActiveSheet
.UsedRange
'reset last cell
strLCell = .Cells.SpecialCells(xlCellTypeLastCell).Address
'get last cell address
.PageSetup.PrintArea = "$A$1:" & strLCell
'set the print area
End With

This method doesn't recognize charts/graphs/objects apparently.

I also tried hiding unused columns/rows (as described on J-Walk.com)
but that didnt seem to work either.

Any help would be appreciated.

--

Dave Peterson