Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to get print area borders - UsedRange not correct.
I would like to get the cell range of the area that will be printed for
a worksheet when no print area is set. I can get the page breaks but they do not include the beginning or ending boundaries. The beginning is easy as I will be printing the entire worksheet. But the end has so far eluded me. I was using UsedRange but found this to not give the correct answer. Case in point, I have a worksheet with 2 columns of data who range is B4:C10. Then I have a chart generated from that data further down and to the right on the worksheet. UsedRange returns A1:C10. In Page Break Preview view mode there is a nice solid border for the print boundary. What I need is this address of the cell at the bottom right corner of this area. The chart's bottom right corner does not match this location. Any suggestions appreciated TIA JO |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to get print area borders - UsedRange not correct.
Interesting question. I guess the only way to find the real last cell is to
work though the objects on the sheet like this: Sub Test() MsgBox RealLastCell.Address End Sub Function RealLastCell() As Range Dim LastCell As Range Dim Obj As Object Dim LastRow As Long, LastCol As Long Set LastCell = Cells.SpecialCells(xlCellTypeLastCell) LastRow = LastCell.Row LastCol = LastCell.Column For Each Obj In ActiveSheet.DrawingObjects If Obj.BottomRightCell.Row LastRow Then LastRow = Obj.BottomRightCell.Row End If If Obj.BottomRightCell.Column LastCol Then LastCol = Obj.BottomRightCell.Column End If Next Set RealLastCell = Cells(LastRow, LastCol) End Function -- Jim "JO" wrote in message ... I would like to get the cell range of the area that will be printed for a worksheet when no print area is set. I can get the page breaks but they do not include the beginning or ending boundaries. The beginning is easy as I will be printing the entire worksheet. But the end has so far eluded me. I was using UsedRange but found this to not give the correct answer. Case in point, I have a worksheet with 2 columns of data who range is B4:C10. Then I have a chart generated from that data further down and to the right on the worksheet. UsedRange returns A1:C10. In Page Break Preview view mode there is a nice solid border for the print boundary. What I need is this address of the cell at the bottom right corner of this area. The chart's bottom right corner does not match this location. Any suggestions appreciated TIA JO |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cell borders do not print on one side of print area?? | Excel Discussion (Misc queries) | |||
Conditional Formatting of merged cells, borders don't show correct | Excel Discussion (Misc queries) | |||
cell borders that I create dont show on print preview or print | Excel Discussion (Misc queries) | |||
How do you turn off a print area for a page? (no print area) | Excel Discussion (Misc queries) | |||
Total of rows in UsedRange Area | Excel Programming |