Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Page break code - hpagebreak before value
Need to have a page break before every instance of *dept* in a column... this
doesn't work. Do multiple columns mess it up? Cells.PageBreak = xlPageBreakNone col = 1 LastRw = 3300 For x = 1 To LastRw If Cells(x, col).Value = "*dept*" Then 'or like "*dept*" ActiveWindow.SelectedSheets.HPageBreaks.Add Befo=Rows(x) End If Next ActiveWindow.View = xlPageBreakPreview 'ActiveSheet.ResetAllPageBreaks End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Page break code - hpagebreak before value
Try the below....Have you tested the other macro...with the sample posted...
Sub MyMacro() Dim lngRow As Long For lngRow = 1 To Cells(Rows.Count, "A").End(xlUp).Row + 1 If lngRow < 1 Then If UCase(Right(Range("A" & lngRow), 5)) = " DEPT" Then ActiveSheet.HPageBreaks.Add Befo=Range("A" & lngRow) End If End If Next End Sub If this post helps click Yes --------------- Jacob Skaria "Maarkr" wrote: Need to have a page break before every instance of *dept* in a column... this doesn't work. Do multiple columns mess it up? Cells.PageBreak = xlPageBreakNone col = 1 LastRw = 3300 For x = 1 To LastRw If Cells(x, col).Value = "*dept*" Then 'or like "*dept*" ActiveWindow.SelectedSheets.HPageBreaks.Add Befo=Rows(x) End If Next ActiveWindow.View = xlPageBreakPreview 'ActiveSheet.ResetAllPageBreaks End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Page break code - hpagebreak before value
it won't work because the text 'dept' is in a different place thruout the
cell... it may be =678899 UFFH Finance Dept ****** or =**884 Marktg Dept Anytown MI so the Right(Range("A" & lngRow), 5)) = " DEPT" won't work... can't I use a wildcard??? "Jacob Skaria" wrote: Try the below....Have you tested the other macro...with the sample posted... Sub MyMacro() Dim lngRow As Long For lngRow = 1 To Cells(Rows.Count, "A").End(xlUp).Row + 1 If lngRow < 1 Then If UCase(Right(Range("A" & lngRow), 5)) = " DEPT" Then ActiveSheet.HPageBreaks.Add Befo=Range("A" & lngRow) End If End If Next End Sub If this post helps click Yes --------------- Jacob Skaria "Maarkr" wrote: Need to have a page break before every instance of *dept* in a column... this doesn't work. Do multiple columns mess it up? Cells.PageBreak = xlPageBreakNone col = 1 LastRw = 3300 For x = 1 To LastRw If Cells(x, col).Value = "*dept*" Then 'or like "*dept*" ActiveWindow.SelectedSheets.HPageBreaks.Add Befo=Rows(x) End If Next ActiveWindow.View = xlPageBreakPreview 'ActiveSheet.ResetAllPageBreaks End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Page break code - hpagebreak before value
OK. Try the below
Sub MyMacro() Dim lngRow As Long For lngRow = 1 To Cells(Rows.Count, "A").End(xlUp).Row + 1 If lngRow < 1 Then If Instr(1, Range("A" & lngRow), " DEPT", vbTextCompare) 0 Then ActiveSheet.HPageBreaks.Add Befo=Range("A" & lngRow) End If End If Next End Sub If this post helps click Yes --------------- Jacob Skaria "Maarkr" wrote: it won't work because the text 'dept' is in a different place thruout the cell... it may be =678899 UFFH Finance Dept ****** or =**884 Marktg Dept Anytown MI so the Right(Range("A" & lngRow), 5)) = " DEPT" won't work... can't I use a wildcard??? "Jacob Skaria" wrote: Try the below....Have you tested the other macro...with the sample posted... Sub MyMacro() Dim lngRow As Long For lngRow = 1 To Cells(Rows.Count, "A").End(xlUp).Row + 1 If lngRow < 1 Then If UCase(Right(Range("A" & lngRow), 5)) = " DEPT" Then ActiveSheet.HPageBreaks.Add Befo=Range("A" & lngRow) End If End If Next End Sub If this post helps click Yes --------------- Jacob Skaria "Maarkr" wrote: Need to have a page break before every instance of *dept* in a column... this doesn't work. Do multiple columns mess it up? Cells.PageBreak = xlPageBreakNone col = 1 LastRw = 3300 For x = 1 To LastRw If Cells(x, col).Value = "*dept*" Then 'or like "*dept*" ActiveWindow.SelectedSheets.HPageBreaks.Add Befo=Rows(x) End If Next ActiveWindow.View = xlPageBreakPreview 'ActiveSheet.ResetAllPageBreaks End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2007 Page Break Adjustments causes a page break each cell | Excel Worksheet Functions | |||
Page break code | Excel Programming | |||
VBA Code-listing page break | Excel Programming | |||
HPageBreak count problem unless in page break view | Excel Programming | |||
Code to insert a Page Break | Excel Programming |