Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
xl97 delete phantom pgBreaks in VBA?
Hello,
I was using an example in the Excel97 help files for looping through a pagebreak collection, but the loop didn't work. For Each pb in Worksheets(1).HPageBreaks ... Next bombs out right at "For Each pb..." even though I do have pagebreaks in the worksheet. So I used a For..Next loop. This was a little better. But if I have 2 pagebreaks that I did insert, the sht.hpagebreak.count says like 6. So I loop through the collection and I see a location for phantom pagebreaks which I cannot remove/delete in VBA. I did this for several sheets in one workbook and got different locations for these phantom pagebreaks. Is this a bug in Excle97 or is there a way to deal with these underlying pagebreaks? Some obscure Excel constant maybe? workbook configuration? Any suggestions appreciated. Thanks, Rich |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
xl97 delete phantom pgBreaks in VBA?
I did one experiment where I set the print area to
something like A1:O42 (my first phantom pb is at A43 in this one sheet). So now the sht.Hpagebreaks.Count is 0. But if I set the the print area to A1:O46, then the phantom pagebreak shows up again. As far as pagebreaks go, the phantom pb's don't interfere with printing operations. But I want to reset the pagebreaks according to how much data I have in a given sheet. It is very difficult to loop through the pb collection with these phantom pb's. If anyone knows how to deal with this - please share. Thanks again, Rich -----Original Message----- Hello, I was using an example in the Excel97 help files for looping through a pagebreak collection, but the loop didn't work. For Each pb in Worksheets(1).HPageBreaks ... Next bombs out right at "For Each pb..." even though I do have pagebreaks in the worksheet. So I used a For..Next loop. This was a little better. But if I have 2 pagebreaks that I did insert, the sht.hpagebreak.count says like 6. So I loop through the collection and I see a location for phantom pagebreaks which I cannot remove/delete in VBA. I did this for several sheets in one workbook and got different locations for these phantom pagebreaks. Is this a bug in Excle97 or is there a way to deal with these underlying pagebreaks? Some obscure Excel constant maybe? workbook configuration? Any suggestions appreciated. Thanks, Rich . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
xl97 delete phantom pgBreaks in VBA?
Sure you are not seeing automatic pagebreaks?
If the length of the page exceeds what can be printed on the printer in one page, excel puts in an automatic pagebreak. No way to get around that other than to put in manual pagebreaks that will fit to the printer page. -- Regards, Tom Ogilvy "Rich" wrote in message ... Hello, I was using an example in the Excel97 help files for looping through a pagebreak collection, but the loop didn't work. For Each pb in Worksheets(1).HPageBreaks ... Next bombs out right at "For Each pb..." even though I do have pagebreaks in the worksheet. So I used a For..Next loop. This was a little better. But if I have 2 pagebreaks that I did insert, the sht.hpagebreak.count says like 6. So I loop through the collection and I see a location for phantom pagebreaks which I cannot remove/delete in VBA. I did this for several sheets in one workbook and got different locations for these phantom pagebreaks. Is this a bug in Excle97 or is there a way to deal with these underlying pagebreaks? Some obscure Excel constant maybe? workbook configuration? Any suggestions appreciated. Thanks, Rich |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
xl97 delete phantom pgBreaks in VBA?
OK. That makes sense. Automatic pagebreaks. But I am
still baffled why if I try to loop through the pb collection with something like Dim pb as pagebreak 'or make pb a variant For each pb in sht.hpagebreaks debug.print pb.location Next this bombs out even if I did insert pagebreaks. If I place the mouse cursor over pb in debug mode it shows pb = nothing even though the pb count is like 6. Any thoughts why For Each..Next not working here? Thanks for your reply, Rich -----Original Message----- Sure you are not seeing automatic pagebreaks? If the length of the page exceeds what can be printed on the printer in one page, excel puts in an automatic pagebreak. No way to get around that other than to put in manual pagebreaks that will fit to the printer page. -- Regards, Tom Ogilvy "Rich" wrote in message ... Hello, I was using an example in the Excel97 help files for looping through a pagebreak collection, but the loop didn't work. For Each pb in Worksheets(1).HPageBreaks ... Next bombs out right at "For Each pb..." even though I do have pagebreaks in the worksheet. So I used a For..Next loop. This was a little better. But if I have 2 pagebreaks that I did insert, the sht.hpagebreak.count says like 6. So I loop through the collection and I see a location for phantom pagebreaks which I cannot remove/delete in VBA. I did this for several sheets in one workbook and got different locations for these phantom pagebreaks. Is this a bug in Excle97 or is there a way to deal with these underlying pagebreaks? Some obscure Excel constant maybe? workbook configuration? Any suggestions appreciated. Thanks, Rich . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
xl97 delete phantom pgBreaks in VBA?
Excel has a feature called the usedrange - it does not store information on
cells outside this range. It is possible that your pagebreaks are outside this range. You can find the bottom of this range with Edit=Goto=Special and select Last Cell. Related is that if you don't have anything to print past a pagebreak, it isn't in the hpagebreak collection. Here is some code you can play with: The output is in the immediate window (in the VBE, choose View=Immediate Window or do Ctrl+G). This is from a past posting I did: ----------------------- Here is a method to get an array of horizontal pagebreaks and vertical pagebreaks. The horizontal pagebreaks are a list of rows that have the pagebreak and vertical a list of column numbers: Sub Tester1() Dim horzpbArray() Dim verpbArray() 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) Debug.Print "Horizontal Pagebreaks (rows):" For J = LBound(horzpbArray, 1) To UBound(horzpbArray, 1) Debug.Print J, horzpbArray(J) Next J i = 1 While Not IsError(Evaluate("Index(vPB," & i & ")")) ReDim Preserve verpbArray(1 To i) verpbArray(i) = Evaluate("Index(vPB," & i & ")") i = i + 1 Wend ReDim Preserve verpbArray(1 To i - 1) Debug.Print "Vertical Pagebreaks (columns):" For J = LBound(verpbArray, 1) To UBound(verpbArray, 1) Debug.Print J, verpbArray(J) Next J End Sub This uses an Excel 4 macro to get this information. This is much faster than the VBA pagebreak which uses the printer driver and can be very slow. The is a pagebreak property of the range. It can be tested to see if a pagebreak exists if rows(6).pagebreak = xlNone then 'No pagebreak Else ' Has pagebreak if rows(6).pagebreak = xlPageBreakAutomatic then 'Automatic pagebreak elseif rows(6).pagebreak = xlPageBreakManual then ' Manual pagebreak End if End if Combining the above gives: 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) Debug.Print "Horizontal Pagebreaks (rows):" For j = LBound(horzpbArray, 1) To UBound(horzpbArray, 1) If Rows(horzpbArray(j)).PageBreak = xlNone Then brkType = "None" Else ' Has pagebreak If Rows(horzpbArray(j)).PageBreak = xlPageBreakAutomatic Then brkType = "Automatic" ElseIf Rows(horzpbArray(j)).PageBreak = xlPageBreakManual Then brkType = "Manual" Else brkType = "Unknown" End If End If Debug.Print j, horzpbArray(j), brkType Next j i = 1 While Not IsError(Evaluate("Index(vPB," & i & ")")) ReDim Preserve verpbArray(1 To i) verpbArray(i) = Evaluate("Index(vPB," & i & ")") i = i + 1 Wend ReDim Preserve verpbArray(1 To i - 1) Debug.Print "Vertical Pagebreaks (columns):" For j = LBound(verpbArray, 1) To UBound(verpbArray, 1) If Columns(verpbArray(j)).PageBreak = xlNone Then brkType = "None" Else ' Has pagebreak If Columns(verpbArray(j)).PageBreak = xlPageBreakAutomatic Then brkType = "Automatic" ElseIf Columns(verpbArray(j)).PageBreak = xlPageBreakManual Then brkType = "Manual" Else brkType = "Unknown" End If End If Debug.Print j, verpbArray(j), brkType Next j End Sub Sample Output: Horizontal Pagebreaks (rows): 1 13 Manual 2 24 Manual 3 39 Manual 4 67 Manual 5 87 Manual 6 114 Automatic Vertical Pagebreaks (columns): 1 2 Manual 2 6 Automatic This should get you started. Regards, Tom Ogilvy "Rich" wrote in message ... OK. That makes sense. Automatic pagebreaks. But I am still baffled why if I try to loop through the pb collection with something like Dim pb as pagebreak 'or make pb a variant For each pb in sht.hpagebreaks debug.print pb.location Next this bombs out even if I did insert pagebreaks. If I place the mouse cursor over pb in debug mode it shows pb = nothing even though the pb count is like 6. Any thoughts why For Each..Next not working here? Thanks for your reply, Rich -----Original Message----- Sure you are not seeing automatic pagebreaks? If the length of the page exceeds what can be printed on the printer in one page, excel puts in an automatic pagebreak. No way to get around that other than to put in manual pagebreaks that will fit to the printer page. -- Regards, Tom Ogilvy "Rich" wrote in message ... Hello, I was using an example in the Excel97 help files for looping through a pagebreak collection, but the loop didn't work. For Each pb in Worksheets(1).HPageBreaks ... Next bombs out right at "For Each pb..." even though I do have pagebreaks in the worksheet. So I used a For..Next loop. This was a little better. But if I have 2 pagebreaks that I did insert, the sht.hpagebreak.count says like 6. So I loop through the collection and I see a location for phantom pagebreaks which I cannot remove/delete in VBA. I did this for several sheets in one workbook and got different locations for these phantom pagebreaks. Is this a bug in Excle97 or is there a way to deal with these underlying pagebreaks? Some obscure Excel constant maybe? workbook configuration? Any suggestions appreciated. Thanks, Rich . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
xl97 delete phantom pgBreaks in VBA?
Wow! This is slick. I had to add some fake data and I
had to add a few hpb's of my own to prime your code. But it was able to detect the automatic pb's in addition to the manual ones I entered. It bombed on vpb's. I need to add some vertical ones to prime. Gotta admit, have never used vertical pb's. I guess now would be a good time to start. So (yes, I'm a bonehead), how do you add a vpb? Thanks, Rich -----Original Message----- Excel has a feature called the usedrange - it does not store information on cells outside this range. It is possible that your pagebreaks are outside this range. You can find the bottom of this range with Edit=Goto=Special and select Last Cell. Related is that if you don't have anything to print past a pagebreak, it isn't in the hpagebreak collection. Here is some code you can play with: The output is in the immediate window (in the VBE, choose View=Immediate Window or do Ctrl+G). This is from a past posting I did: ----------------------- Here is a method to get an array of horizontal pagebreaks and vertical pagebreaks. The horizontal pagebreaks are a list of rows that have the pagebreak and vertical a list of column numbers: Sub Tester1() Dim horzpbArray() Dim verpbArray() 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) Debug.Print "Horizontal Pagebreaks (rows):" For J = LBound(horzpbArray, 1) To UBound(horzpbArray, 1) Debug.Print J, horzpbArray(J) Next J i = 1 While Not IsError(Evaluate("Index(vPB," & i & ")")) ReDim Preserve verpbArray(1 To i) verpbArray(i) = Evaluate("Index(vPB," & i & ")") i = i + 1 Wend ReDim Preserve verpbArray(1 To i - 1) Debug.Print "Vertical Pagebreaks (columns):" For J = LBound(verpbArray, 1) To UBound(verpbArray, 1) Debug.Print J, verpbArray(J) Next J End Sub This uses an Excel 4 macro to get this information. This is much faster than the VBA pagebreak which uses the printer driver and can be very slow. The is a pagebreak property of the range. It can be tested to see if a pagebreak exists if rows(6).pagebreak = xlNone then 'No pagebreak Else ' Has pagebreak if rows(6).pagebreak = xlPageBreakAutomatic then 'Automatic pagebreak elseif rows(6).pagebreak = xlPageBreakManual then ' Manual pagebreak End if End if Combining the above gives: 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) Debug.Print "Horizontal Pagebreaks (rows):" For j = LBound(horzpbArray, 1) To UBound(horzpbArray, 1) If Rows(horzpbArray(j)).PageBreak = xlNone Then brkType = "None" Else ' Has pagebreak If Rows(horzpbArray(j)).PageBreak = xlPageBreakAutomatic Then brkType = "Automatic" ElseIf Rows(horzpbArray(j)).PageBreak = xlPageBreakManual Then brkType = "Manual" Else brkType = "Unknown" End If End If Debug.Print j, horzpbArray(j), brkType Next j i = 1 While Not IsError(Evaluate("Index(vPB," & i & ")")) ReDim Preserve verpbArray(1 To i) verpbArray(i) = Evaluate("Index(vPB," & i & ")") i = i + 1 Wend ReDim Preserve verpbArray(1 To i - 1) Debug.Print "Vertical Pagebreaks (columns):" For j = LBound(verpbArray, 1) To UBound(verpbArray, 1) If Columns(verpbArray(j)).PageBreak = xlNone Then brkType = "None" Else ' Has pagebreak If Columns(verpbArray(j)).PageBreak = xlPageBreakAutomatic Then brkType = "Automatic" ElseIf Columns(verpbArray(j)).PageBreak = xlPageBreakManual Then brkType = "Manual" Else brkType = "Unknown" End If End If Debug.Print j, verpbArray(j), brkType Next j End Sub Sample Output: Horizontal Pagebreaks (rows): 1 13 Manual 2 24 Manual 3 39 Manual 4 67 Manual 5 87 Manual 6 114 Automatic Vertical Pagebreaks (columns): 1 2 Manual 2 6 Automatic This should get you started. Regards, Tom Ogilvy "Rich" wrote in message ... OK. That makes sense. Automatic pagebreaks. But I am still baffled why if I try to loop through the pb collection with something like Dim pb as pagebreak 'or make pb a variant For each pb in sht.hpagebreaks debug.print pb.location Next this bombs out even if I did insert pagebreaks. If I place the mouse cursor over pb in debug mode it shows pb = nothing even though the pb count is like 6. Any thoughts why For Each..Next not working here? Thanks for your reply, Rich -----Original Message----- Sure you are not seeing automatic pagebreaks? If the length of the page exceeds what can be printed on the printer in one page, excel puts in an automatic pagebreak. No way to get around that other than to put in manual pagebreaks that will fit to the printer page. -- Regards, Tom Ogilvy "Rich" wrote in message ... Hello, I was using an example in the Excel97 help files for looping through a pagebreak collection, but the loop didn't work. For Each pb in Worksheets(1).HPageBreaks ... Next bombs out right at "For Each pb..." even though I do have pagebreaks in the worksheet. So I used a For..Next loop. This was a little better. But if I have 2 pagebreaks that I did insert, the sht.hpagebreak.count says like 6. So I loop through the collection and I see a location for phantom pagebreaks which I cannot remove/delete in VBA. I did this for several sheets in one workbook and got different locations for these phantom pagebreaks. Is this a bug in Excle97 or is there a way to deal with these underlying pagebreaks? Some obscure Excel constant maybe? workbook configuration? Any suggestions appreciated. Thanks, Rich . . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
xl97 delete phantom pgBreaks in VBA?
Nevermind on the vpb. I figured it out :). Thanks for
the example. Rich -----Original Message----- Wow! This is slick. I had to add some fake data and I had to add a few hpb's of my own to prime your code. But it was able to detect the automatic pb's in addition to the manual ones I entered. It bombed on vpb's. I need to add some vertical ones to prime. Gotta admit, have never used vertical pb's. I guess now would be a good time to start. So (yes, I'm a bonehead), how do you add a vpb? Thanks, Rich -----Original Message----- Excel has a feature called the usedrange - it does not store information on cells outside this range. It is possible that your pagebreaks are outside this range. You can find the bottom of this range with Edit=Goto=Special and select Last Cell. Related is that if you don't have anything to print past a pagebreak, it isn't in the hpagebreak collection. Here is some code you can play with: The output is in the immediate window (in the VBE, choose View=Immediate Window or do Ctrl+G). This is from a past posting I did: ----------------------- Here is a method to get an array of horizontal pagebreaks and vertical pagebreaks. The horizontal pagebreaks are a list of rows that have the pagebreak and vertical a list of column numbers: Sub Tester1() Dim horzpbArray() Dim verpbArray() 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) Debug.Print "Horizontal Pagebreaks (rows):" For J = LBound(horzpbArray, 1) To UBound(horzpbArray, 1) Debug.Print J, horzpbArray(J) Next J i = 1 While Not IsError(Evaluate("Index(vPB," & i & ")")) ReDim Preserve verpbArray(1 To i) verpbArray(i) = Evaluate("Index(vPB," & i & ")") i = i + 1 Wend ReDim Preserve verpbArray(1 To i - 1) Debug.Print "Vertical Pagebreaks (columns):" For J = LBound(verpbArray, 1) To UBound(verpbArray, 1) Debug.Print J, verpbArray(J) Next J End Sub This uses an Excel 4 macro to get this information. This is much faster than the VBA pagebreak which uses the printer driver and can be very slow. The is a pagebreak property of the range. It can be tested to see if a pagebreak exists if rows(6).pagebreak = xlNone then 'No pagebreak Else ' Has pagebreak if rows(6).pagebreak = xlPageBreakAutomatic then 'Automatic pagebreak elseif rows(6).pagebreak = xlPageBreakManual then ' Manual pagebreak End if End if Combining the above gives: 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) Debug.Print "Horizontal Pagebreaks (rows):" For j = LBound(horzpbArray, 1) To UBound(horzpbArray, 1) If Rows(horzpbArray(j)).PageBreak = xlNone Then brkType = "None" Else ' Has pagebreak If Rows(horzpbArray(j)).PageBreak = xlPageBreakAutomatic Then brkType = "Automatic" ElseIf Rows(horzpbArray(j)).PageBreak = xlPageBreakManual Then brkType = "Manual" Else brkType = "Unknown" End If End If Debug.Print j, horzpbArray(j), brkType Next j i = 1 While Not IsError(Evaluate("Index(vPB," & i & ")")) ReDim Preserve verpbArray(1 To i) verpbArray(i) = Evaluate("Index(vPB," & i & ")") i = i + 1 Wend ReDim Preserve verpbArray(1 To i - 1) Debug.Print "Vertical Pagebreaks (columns):" For j = LBound(verpbArray, 1) To UBound(verpbArray, 1) If Columns(verpbArray(j)).PageBreak = xlNone Then brkType = "None" Else ' Has pagebreak If Columns(verpbArray(j)).PageBreak = xlPageBreakAutomatic Then brkType = "Automatic" ElseIf Columns(verpbArray(j)).PageBreak = xlPageBreakManual Then brkType = "Manual" Else brkType = "Unknown" End If End If Debug.Print j, verpbArray(j), brkType Next j End Sub Sample Output: Horizontal Pagebreaks (rows): 1 13 Manual 2 24 Manual 3 39 Manual 4 67 Manual 5 87 Manual 6 114 Automatic Vertical Pagebreaks (columns): 1 2 Manual 2 6 Automatic This should get you started. Regards, Tom Ogilvy "Rich" wrote in message ... OK. That makes sense. Automatic pagebreaks. But I am still baffled why if I try to loop through the pb collection with something like Dim pb as pagebreak 'or make pb a variant For each pb in sht.hpagebreaks debug.print pb.location Next this bombs out even if I did insert pagebreaks. If I place the mouse cursor over pb in debug mode it shows pb = nothing even though the pb count is like 6. Any thoughts why For Each..Next not working here? Thanks for your reply, Rich -----Original Message----- Sure you are not seeing automatic pagebreaks? If the length of the page exceeds what can be printed on the printer in one page, excel puts in an automatic pagebreak. No way to get around that other than to put in manual pagebreaks that will fit to the printer page. -- Regards, Tom Ogilvy "Rich" wrote in message ... Hello, I was using an example in the Excel97 help files for looping through a pagebreak collection, but the loop didn't work. For Each pb in Worksheets(1).HPageBreaks ... Next bombs out right at "For Each pb..." even though I do have pagebreaks in the worksheet. So I used a For..Next loop. This was a little better. But if I have 2 pagebreaks that I did insert, the sht.hpagebreak.count says like 6. So I loop through the collection and I see a location for phantom pagebreaks which I cannot remove/delete in VBA. I did this for several sheets in one workbook and got different locations for these phantom pagebreaks. Is this a bug in Excle97 or is there a way to deal with these underlying pagebreaks? Some obscure Excel constant maybe? workbook configuration? Any suggestions appreciated. Thanks, Rich . . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Phantom borders. Can't delete. | Excel Discussion (Misc queries) | |||
LEN function in XL97 | Excel Discussion (Misc queries) | |||
XL97 changes filenames upon open | Excel Discussion (Misc queries) | |||
xl97 and Worksheet_Change event ? | Excel Programming | |||
UDF returns #value in XL97 | Excel Programming |