Driving me nuts! Row Height, Cell Width
search for the horizontal and vertical pagebreaks.
--
Regards,
Tom Ogilvy
"Ed" wrote in message
...
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
|