| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Page Break
Hello everyone,
I not sure if there is such a thing but here goes. I'd like a page break each time the first 4 characters of a value changes in Column A. To make my problem even harder for me is that there are blank cells in Column A. Finally, to make my problem impossible for me is that the page breaks would have to skip the final blank cell in a group and break just below it. It's difficult to explain but I hope the example below helps clarify what I'm saying. Here's what I have: A Starke Starke Starke (blank cell) St Augstine St Augustine (blank cell) St Augustine 2 St Augustine 2 (blank cell) St Johns St Johns (blank cell) Here are the desired results A Starke Starke Starke (blank cell) with page break just below St Augstine St Augustine (blank cell) St Augustine 2 St Augustine 2 (blank cell) with page break just below St Johns St Johns (blank cell) with page break just below St Augustine and St Augustine 2 are in the same group because the first 4 characters did not change. I have to keep the blank cells intact. Can this be done with code? Many thanks for looking, -- Mike Jacksonville, Florida |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Conditional Page Break
Hi Mike,
You will probably need to use VBA, is that an option for you? -- Cheers, Shane Devenshire "Mike Saffer" wrote: Hello everyone, I not sure if there is such a thing but here goes. I'd like a page break each time the first 4 characters of a value changes in Column A. To make my problem even harder for me is that there are blank cells in Column A. Finally, to make my problem impossible for me is that the page breaks would have to skip the final blank cell in a group and break just below it. It's difficult to explain but I hope the example below helps clarify what I'm saying. Here's what I have: A Starke Starke Starke (blank cell) St Augstine St Augustine (blank cell) St Augustine 2 St Augustine 2 (blank cell) St Johns St Johns (blank cell) Here are the desired results A Starke Starke Starke (blank cell) with page break just below St Augstine St Augustine (blank cell) St Augustine 2 St Augustine 2 (blank cell) with page break just below St Johns St Johns (blank cell) with page break just below St Augustine and St Augustine 2 are in the same group because the first 4 characters did not change. I have to keep the blank cells intact. Can this be done with code? Many thanks for looking, -- Mike Jacksonville, Florida |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Conditional Page Break
Hello Shane,
I'm somewhat familiar with the VB editor, creating simple modules, editing macros I have recorded, etc. I am not experienced enough to actually write the code myself for this one yet. -- Mike Jacksonville, Florida "ShaneDevenshire" wrote: Hi Mike, You will probably need to use VBA, is that an option for you? -- Cheers, Shane Devenshire "Mike Saffer" wrote: Hello everyone, I not sure if there is such a thing but here goes. I'd like a page break each time the first 4 characters of a value changes in Column A. To make my problem even harder for me is that there are blank cells in Column A. Finally, to make my problem impossible for me is that the page breaks would have to skip the final blank cell in a group and break just below it. It's difficult to explain but I hope the example below helps clarify what I'm saying. Here's what I have: A Starke Starke Starke (blank cell) St Augstine St Augustine (blank cell) St Augustine 2 St Augustine 2 (blank cell) St Johns St Johns (blank cell) Here are the desired results A Starke Starke Starke (blank cell) with page break just below St Augstine St Augustine (blank cell) St Augustine 2 St Augustine 2 (blank cell) with page break just below St Johns St Johns (blank cell) with page break just below St Augustine and St Augustine 2 are in the same group because the first 4 characters did not change. I have to keep the blank cells intact. Can this be done with code? Many thanks for looking, -- Mike Jacksonville, Florida |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Conditional Page Break
Hi
Try to see if this macro is what you want: Sub test() lastrow = Range("a65536").End(xlUp).Row ActiveSheet.ResetAllPageBreaks ActiveWindow.ActiveSheet.HPageBreaks.Add befo=Cells(lastrow + 2, "A") Val1 = Left(Range("A1").Value, 4) For r = 2 To lastrow If Cells(r, "A").Value = "" Then If Not Left(Cells(r + 1, "A").Value, 4) = Val1 Then ActiveSheet.HPageBreaks.Add befo=Cells(r + 1, "A") Val1 = Cells(r + 1, "A").Value End If End If Next End Sub Regards, Per "Mike Saffer" skrev i meddelelsen ... Hello everyone, I not sure if there is such a thing but here goes. I'd like a page break each time the first 4 characters of a value changes in Column A. To make my problem even harder for me is that there are blank cells in Column A. Finally, to make my problem impossible for me is that the page breaks would have to skip the final blank cell in a group and break just below it. It's difficult to explain but I hope the example below helps clarify what I'm saying. Here's what I have: A Starke Starke Starke (blank cell) St Augstine St Augustine (blank cell) St Augustine 2 St Augustine 2 (blank cell) St Johns St Johns (blank cell) Here are the desired results A Starke Starke Starke (blank cell) with page break just below St Augstine St Augustine (blank cell) St Augustine 2 St Augustine 2 (blank cell) with page break just below St Johns St Johns (blank cell) with page break just below St Augustine and St Augustine 2 are in the same group because the first 4 characters did not change. I have to keep the blank cells intact. Can this be done with code? Many thanks for looking, -- Mike Jacksonville, Florida |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Conditional Page Break
Hello Per,
Your macro did indeed put the page break after the blank cells separating the groups. The only thing it did not do was keep the groups together. After I ran your macro I got this: Starke Starke Starke (blank cell) with page break just below St Augstine St Augustine (blank cell) with page break just below***** St Augustine 2 St Augustine 2 (blank cell) with page break just below St Johns St Johns (blank cell) with page break just below ***** Because the first 4 characters in St Augustine and St Augustine 2 are the same (ST blank space A) I was hoping the page break would come after only St Augustine 2's blank cell instead of after both St Augustine and St Augustine 2's blank cells. I'd like a page break each time the first 4 characters of a value changes in Column A. Otherwise it "ungroups" the St Augustines and prints out way too many pages. Per, thank you for taking the time to look at my problem here. -- Mike Jacksonville, Florida "Per Jessen" wrote: Hi Try to see if this macro is what you want: Sub test() lastrow = Range("a65536").End(xlUp).Row ActiveSheet.ResetAllPageBreaks ActiveWindow.ActiveSheet.HPageBreaks.Add befo=Cells(lastrow + 2, "A") Val1 = Left(Range("A1").Value, 4) For r = 2 To lastrow If Cells(r, "A").Value = "" Then If Not Left(Cells(r + 1, "A").Value, 4) = Val1 Then ActiveSheet.HPageBreaks.Add befo=Cells(r + 1, "A") Val1 = Cells(r + 1, "A").Value End If End If Next End Sub Regards, Per "Mike Saffer" skrev i meddelelsen ... Hello everyone, I not sure if there is such a thing but here goes. I'd like a page break each time the first 4 characters of a value changes in Column A. To make my problem even harder for me is that there are blank cells in Column A. Finally, to make my problem impossible for me is that the page breaks would have to skip the final blank cell in a group and break just below it. It's difficult to explain but I hope the example below helps clarify what I'm saying. Here's what I have: A Starke Starke Starke (blank cell) St Augstine St Augustine (blank cell) St Augustine 2 St Augustine 2 (blank cell) St Johns St Johns (blank cell) Here are the desired results A Starke Starke Starke (blank cell) with page break just below St Augstine St Augustine (blank cell) St Augustine 2 St Augustine 2 (blank cell) with page break just below St Johns St Johns (blank cell) with page break just below St Augustine and St Augustine 2 are in the same group because the first 4 characters did not change. I have to keep the blank cells intact. Can this be done with code? Many thanks for looking, -- Mike Jacksonville, Florida |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Conditional Page Break
Hi Mike
Thanks for your reply. I just forgot a little detail to make it work as desired :-( Try this: Sub InsertPageBreaks() lastrow = Range("a65536").End(xlUp).Row ActiveSheet.ResetAllPageBreaks ActiveWindow.ActiveSheet.HPageBreaks.Add befo=Cells(lastrow + 2, "A") val1 = Left(Range("A1").Value, 4) For r = 2 To lastrow If Cells(r, "A").Value = "" Then If Not Left(Cells(r + 1, "A").Value, 4) = val1 Then ActiveSheet.HPageBreaks.Add befo=Cells(r + 1, "A") val1 = Left(Cells(r + 1, "A").Value, 4) End If End If Next End Sub Best regards, Per "Mike Saffer" skrev i meddelelsen ... Hello Per, Your macro did indeed put the page break after the blank cells separating the groups. The only thing it did not do was keep the groups together. After I ran your macro I got this: Starke Starke Starke (blank cell) with page break just below St Augstine St Augustine (blank cell) with page break just below***** St Augustine 2 St Augustine 2 (blank cell) with page break just below St Johns St Johns (blank cell) with page break just below ***** Because the first 4 characters in St Augustine and St Augustine 2 are the same (ST blank space A) I was hoping the page break would come after only St Augustine 2's blank cell instead of after both St Augustine and St Augustine 2's blank cells. I'd like a page break each time the first 4 characters of a value changes in Column A. Otherwise it "ungroups" the St Augustines and prints out way too many pages. Per, thank you for taking the time to look at my problem here. -- Mike Jacksonville, Florida "Per Jessen" wrote: Hi Try to see if this macro is what you want: Sub test() lastrow = Range("a65536").End(xlUp).Row ActiveSheet.ResetAllPageBreaks ActiveWindow.ActiveSheet.HPageBreaks.Add befo=Cells(lastrow + 2, "A") Val1 = Left(Range("A1").Value, 4) For r = 2 To lastrow If Cells(r, "A").Value = "" Then If Not Left(Cells(r + 1, "A").Value, 4) = Val1 Then ActiveSheet.HPageBreaks.Add befo=Cells(r + 1, "A") Val1 = Cells(r + 1, "A").Value End If End If Next End Sub Regards, Per "Mike Saffer" skrev i meddelelsen ... Hello everyone, I not sure if there is such a thing but here goes. I'd like a page break each time the first 4 characters of a value changes in Column A. To make my problem even harder for me is that there are blank cells in Column A. Finally, to make my problem impossible for me is that the page breaks would have to skip the final blank cell in a group and break just below it. It's difficult to explain but I hope the example below helps clarify what I'm saying. Here's what I have: A Starke Starke Starke (blank cell) St Augstine St Augustine (blank cell) St Augustine 2 St Augustine 2 (blank cell) St Johns St Johns (blank cell) Here are the desired results A Starke Starke Starke (blank cell) with page break just below St Augstine St Augustine (blank cell) St Augustine 2 St Augustine 2 (blank cell) with page break just below St Johns St Johns (blank cell) with page break just below St Augustine and St Augustine 2 are in the same group because the first 4 characters did not change. I have to keep the blank cells intact. Can this be done with code? Many thanks for looking, -- Mike Jacksonville, Florida |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Conditional Page Break
Outstanding job Per,
The macro worked and the page breaks are now where I needed them. I am much obliged to you and this forum. Thank you very much. -- Mike Jacksonville, Florida "Per Jessen" wrote: Hi Mike Thanks for your reply. I just forgot a little detail to make it work as desired :-( Try this: Sub InsertPageBreaks() lastrow = Range("a65536").End(xlUp).Row ActiveSheet.ResetAllPageBreaks ActiveWindow.ActiveSheet.HPageBreaks.Add befo=Cells(lastrow + 2, "A") val1 = Left(Range("A1").Value, 4) For r = 2 To lastrow If Cells(r, "A").Value = "" Then If Not Left(Cells(r + 1, "A").Value, 4) = val1 Then ActiveSheet.HPageBreaks.Add befo=Cells(r + 1, "A") val1 = Left(Cells(r + 1, "A").Value, 4) End If End If Next End Sub Best regards, Per "Mike Saffer" skrev i meddelelsen ... Hello Per, Your macro did indeed put the page break after the blank cells separating the groups. The only thing it did not do was keep the groups together. After I ran your macro I got this: Starke Starke Starke (blank cell) with page break just below St Augstine St Augustine (blank cell) with page break just below***** St Augustine 2 St Augustine 2 (blank cell) with page break just below St Johns St Johns (blank cell) with page break just below ***** Because the first 4 characters in St Augustine and St Augustine 2 are the same (ST blank space A) I was hoping the page break would come after only St Augustine 2's blank cell instead of after both St Augustine and St Augustine 2's blank cells. I'd like a page break each time the first 4 characters of a value changes in Column A. Otherwise it "ungroups" the St Augustines and prints out way too many pages. Per, thank you for taking the time to look at my problem here. -- Mike Jacksonville, Florida "Per Jessen" wrote: Hi Try to see if this macro is what you want: Sub test() lastrow = Range("a65536").End(xlUp).Row ActiveSheet.ResetAllPageBreaks ActiveWindow.ActiveSheet.HPageBreaks.Add befo=Cells(lastrow + 2, "A") Val1 = Left(Range("A1").Value, 4) For r = 2 To lastrow If Cells(r, "A").Value = "" Then If Not Left(Cells(r + 1, "A").Value, 4) = Val1 Then ActiveSheet.HPageBreaks.Add befo=Cells(r + 1, "A") Val1 = Cells(r + 1, "A").Value End If End If Next End Sub Regards, Per "Mike Saffer" skrev i meddelelsen ... Hello everyone, I not sure if there is such a thing but here goes. I'd like a page break each time the first 4 characters of a value changes in Column A. To make my problem even harder for me is that there are blank cells in Column A. Finally, to make my problem impossible for me is that the page breaks would have to skip the final blank cell in a group and break just below it. It's difficult to explain but I hope the example below helps clarify what I'm saying. Here's what I have: A Starke Starke Starke (blank cell) St Augstine St Augustine (blank cell) St Augustine 2 St Augustine 2 (blank cell) St Johns St Johns (blank cell) Here are the desired results A Starke Starke Starke (blank cell) with page break just below St Augstine St Augustine (blank cell) St Augustine 2 St Augustine 2 (blank cell) with page break just below St Johns St Johns (blank cell) with page break just below St Augustine and St Augustine 2 are in the same group because the first 4 characters did not change. I have to keep the blank cells intact. Can this be done with code? Many thanks for looking, -- Mike Jacksonville, Florida |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Conditional Page Break
Hi Mike
Thanks for your reply. I'm glad to help. Regards, Per Copenhagen, Denmark "Mike Saffer" skrev i meddelelsen ... Outstanding job Per, The macro worked and the page breaks are now where I needed them. I am much obliged to you and this forum. Thank you very much. -- Mike Jacksonville, Florida "Per Jessen" wrote: Hi Mike Thanks for your reply. I just forgot a little detail to make it work as desired :-( Try this: Sub InsertPageBreaks() lastrow = Range("a65536").End(xlUp).Row ActiveSheet.ResetAllPageBreaks ActiveWindow.ActiveSheet.HPageBreaks.Add befo=Cells(lastrow + 2, "A") val1 = Left(Range("A1").Value, 4) For r = 2 To lastrow If Cells(r, "A").Value = "" Then If Not Left(Cells(r + 1, "A").Value, 4) = val1 Then ActiveSheet.HPageBreaks.Add befo=Cells(r + 1, "A") val1 = Left(Cells(r + 1, "A").Value, 4) End If End If Next End Sub Best regards, Per "Mike Saffer" skrev i meddelelsen ... Hello Per, Your macro did indeed put the page break after the blank cells separating the groups. The only thing it did not do was keep the groups together. After I ran your macro I got this: Starke Starke Starke (blank cell) with page break just below St Augstine St Augustine (blank cell) with page break just below***** St Augustine 2 St Augustine 2 (blank cell) with page break just below St Johns St Johns (blank cell) with page break just below ***** Because the first 4 characters in St Augustine and St Augustine 2 are the same (ST blank space A) I was hoping the page break would come after only St Augustine 2's blank cell instead of after both St Augustine and St Augustine 2's blank cells. I'd like a page break each time the first 4 characters of a value changes in Column A. Otherwise it "ungroups" the St Augustines and prints out way too many pages. Per, thank you for taking the time to look at my problem here. -- Mike Jacksonville, Florida "Per Jessen" wrote: Hi Try to see if this macro is what you want: Sub test() lastrow = Range("a65536").End(xlUp).Row ActiveSheet.ResetAllPageBreaks ActiveWindow.ActiveSheet.HPageBreaks.Add befo=Cells(lastrow + 2, "A") Val1 = Left(Range("A1").Value, 4) For r = 2 To lastrow If Cells(r, "A").Value = "" Then If Not Left(Cells(r + 1, "A").Value, 4) = Val1 Then ActiveSheet.HPageBreaks.Add befo=Cells(r + 1, "A") Val1 = Cells(r + 1, "A").Value End If End If Next End Sub Regards, Per "Mike Saffer" skrev i meddelelsen ... Hello everyone, I not sure if there is such a thing but here goes. I'd like a page break each time the first 4 characters of a value changes in Column A. To make my problem even harder for me is that there are blank cells in Column A. Finally, to make my problem impossible for me is that the page breaks would have to skip the final blank cell in a group and break just below it. It's difficult to explain but I hope the example below helps clarify what I'm saying. Here's what I have: A Starke Starke Starke (blank cell) St Augstine St Augustine (blank cell) St Augustine 2 St Augustine 2 (blank cell) St Johns St Johns (blank cell) Here are the desired results A Starke Starke Starke (blank cell) with page break just below St Augstine St Augustine (blank cell) St Augustine 2 St Augustine 2 (blank cell) with page break just below St Johns St Johns (blank cell) with page break just below St Augustine and St Augustine 2 are in the same group because the first 4 characters did not change. I have to keep the blank cells intact. Can this be done with code? Many thanks for looking, -- Mike Jacksonville, Florida |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I do page breaks when view menu doesnt page break preview | Excel Discussion (Misc queries) | |||
| Conditional Formatting - Before Page Break | Excel Discussion (Misc queries) | |||
| change and/or remove page number watermark in page break preview | Excel Discussion (Misc queries) | |||
| Conditional page break if merged rows won't fit on the page. | Excel Worksheet Functions | |||
| adding a new page break to an existing page break | Excel Discussion (Misc queries) | |||
Hybrid Mode
