Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Driving me nuts! Row Height, Cell Width
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Driving me nuts! Row Height, Cell Width
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" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Driving me nuts! Row Height, Cell Width
Here is a post I did that might help:
http://groups-beta.google.com/group/...8?dmode=source -- Regards, Tom Ogilvy "Ed" wrote in message ... 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" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Driving me nuts! Row Height, Cell Width
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 "Tom Ogilvy" wrote in message ... Here is a post I did that might help: http://groups-beta.google.com/group/...8?dmode=source -- Regards, Tom Ogilvy "Ed" wrote in message ... 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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
XL2003: "Auto row height" with Wrap Text driving me nuts | Excel Discussion (Misc queries) | |||
Need Help, this is driving me nuts | Excel Discussion (Misc queries) | |||
question driving me nuts | Excel Discussion (Misc queries) | |||
Driving me nuts. Need more nested than 7 | Excel Discussion (Misc queries) | |||
Excel / VB is driving me nuts!! | Excel Worksheet Functions |