Driving me nuts! Row Height, Cell Width

[Ed]

I want to put a border around the range of cells that will print. I figured
if I know how wide my page is between the margins side to side and top to
bottom, I can simply add the widths of all the columns across and rows down
until I hit my page space limits, find the cells that fit that range and set
a border.

Unfortunately, I keep getting 'way off! The following code should (I
thought!!) get me to the bottom right cell that would show on a page.
According to my Print Preview, that's T66. According to my code, though,
it's Q51! What am I not considering?

Ed

Sub CheckRngSize()

Dim rng As Range
Dim endCol As Long
Dim endRow As Long
Dim numTall As Long
Dim numWide As Long
Dim howTall As Long
Dim howWide As Long

endCol = 1
howWide = 0
endRow = 1
howTall = 0

With ActiveSheet.PageSetup
If .Orientation = xlLandscape Then
numTall = Application.InchesToPoints(8)
numWide = Application.InchesToPoints(11)
Else:
numTall = Application.InchesToPoints(11)
numWide = Application.InchesToPoints(8)
End If
End With

Do
howWide = howWide + ActiveSheet.Columns(endCol).Width
endCol = endCol + 1
Loop Until howWide > numWide
endCol = endCol - 1

Do
howTall = howTall + ActiveSheet.Rows(endRow).Height
endRow = endRow + 1
Loop Until howTall > numTall
endRow = endRow - 1

ActiveSheet.Cells(endRow, endCol).Select

End Sub

 

[Tom Ogilvy]

search for the horizontal and vertical pagebreaks.

 

[Ed]

Tom: Just spent over an hour and a half Googling the NG and trying
different Subs and Functions offered up to no avail. This simple test,
posted by Rob van Gelder, told me the bad news - Excel says I have NO page
breaks? Or am I doing something worng here?

Ed

Sub test()
Dim hpb As HPageBreak

For Each hpb In Sheet1.HPageBreaks
If hpb.Type = xlPageBreakAutomatic Then
Debug.Print "Automatic: " & hpb.Location.Address
ElseIf hpb.Type = xlPageBreakManual Then
Debug.Print "Manual: " & hpb.Location.Address
End If
Next
End Sub

 

[Tom Ogilvy]

Here is a post I did that might help:

http://groups-beta.google.com/group...programming/msg/d100992b75f9a898?dmode=source

 

[Ed]

Tom: I copied your final code and ran it on a worksheet (named "Sheet1")
that in Print Preview says I have six pages, which should yield five
horizontal page breaks. I get a "Subscript out of range" error at the place
indicated below, which seems to tell me it's not picking up my page breaks -
the same thing I've been running into all along. You mentioned printer
drivers - maybe this just doesn't work with some printers and drivers?

Ed

Sub Tester1()
Dim horzpbArray()
Dim verpbArray()
Dim brkType As String
ThisWorkbook.Names.Add Name:="hzPB", _
RefersToR1C1:="=GET.DOCUMENT(64,""Sheet1"")"
ThisWorkbook.Names.Add Name:="vPB", _
RefersToR1C1:="=GET.DOCUMENT(65,""Sheet1"")"
i = 1
While Not IsError(Evaluate("Index(hzPB," & i & ")"))
ReDim Preserve horzpbArray(1 To i)
horzpbArray(i) = Evaluate("Index(hzPB," & i & ")")
i = i + 1
Wend
ReDim Preserve horzpbArray(1 To i - 1) **** Error Here