Excel reading deleted cell w/ data as existing cell w/ data

[wangh3]

Hi!

I have an issue with setting up page breaks after running my code. The part I'm having issues with is one part of the code deletes data at the bottom of the page and then when I run my page break code, it ends up including the blank cells that were deleted at the end. I just want the page break to move up to the next actual row of data. Please help! Also, sorry for my poor VBA, most of it was manipulated or recorded.

<font face=Calibri><SPAN style="color:#00007F">Sub</SPAN> PageBreak_AltHierarchyLadder()<br>    <br>    <SPAN style="color:#007F00">'Sets Up All Page Breaks</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> CellRange <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">Dim</SPAN> TestCell <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">Dim</SPAN> CellRange2 <SPAN style="color:#00007F">As</SPAN> Range<br>        <br>    ActiveSheet.ResetAllPageBreaks<br>    <br>    <SPAN style="color:#00007F">Set</SPAN> CellRange = Range("B66", "AB4409")<br>    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> TestCell <SPAN style="color:#00007F">In</SPAN> CellRange<br>        <SPAN style="color:#00007F">If</SPAN> TestCell.Value = "Act Sales $" <SPAN style="color:#00007F">Then</SPAN><br>            ActiveSheet.Rows(TestCell.Row).PageBreak = xlPageBreakManual<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN> TestCell<br>    <br> <br>    <SPAN style="color:#007F00">'Deletes out unneccesary columns</SPAN><br>    Columns(21).EntireColumn.Delete<br>    Columns(22).EntireColumn.Delete<br>    Columns(23).EntireColumn.Delete<br>    Columns(24).EntireColumn.Delete<br>    Columns(25).EntireColumn.Delete<br>    Columns(26).EntireColumn.Delete<br>    Columns(27).EntireColumn.Delete<br>    Columns(28).EntireColumn.Delete<br>    Columns(29).EntireColumn.Delete<br>    <br>    <SPAN style="color:#007F00">'Auto Fits All Columns</SPAN><br>    Cells.EntireColumn.AutoFit<br>    Columns("B:C").Select<br>    Selection.ColumnWidth = 13<br><br>    <SPAN style="color:#007F00">'Formats text to black color</SPAN><br>    Columns("T:AB").Select<br>    Range("T2").Activate<br>    <SPAN style="color:#00007F">With</SPAN> Selection.Font<br>        .ColorIndex = xlAutomatic<br>        .TintAndShade = 0<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <br>    <SPAN style="color:#007F00">'Formats all cells with Border</SPAN><br>    Cells.Select<br>    Selection.Borders(xlDiagonalDown).LineStyle = xlNone<br>    Selection.Borders(xlDiagonalUp).LineStyle = xlNone<br>    <SPAN style="color:#00007F">With</SPAN> Selection.Borders(xlEdgeLeft)<br>        .LineStyle = xlContinuous<br>        .ColorIndex = 0<br>        .TintAndShade = 0<br>        .Weight = xlThin<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <SPAN style="color:#00007F">With</SPAN> Selection.Borders(xlEdgeTop)<br>        .LineStyle = xlContinuous<br>        .ColorIndex = 0<br>        .TintAndShade = 0<br>        .Weight = xlThin<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <SPAN style="color:#00007F">With</SPAN> Selection.Borders(xlEdgeBottom)<br>        .LineStyle = xlContinuous<br>        .ColorIndex = 0<br>        .TintAndShade = 0<br>        .Weight = xlThin<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <SPAN style="color:#00007F">With</SPAN> Selection.Borders(xlEdgeRight)<br>        .LineStyle = xlContinuous<br>        .ColorIndex = 0<br>        .TintAndShade = 0<br>        .Weight = xlThin<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <SPAN style="color:#00007F">With</SPAN> Selection.Borders(xlInsideVertical)<br>        .LineStyle = xlContinuous<br>        .ColorIndex = 0<br>        .TintAndShade = 0<br>        .Weight = xlThin<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>   <SPAN style="color:#00007F">With</SPAN> Selection.Borders(xlInsideHorizontal)<br>        .LineStyle = xlContinuous<br>        .ColorIndex = 0<br>        .TintAndShade = 0<br>        .Weight = xlThin<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><br><br>    <SPAN style="color:#007F00">'Delete Total Rows</SPAN><br>    LR = Range("A" & Rows.Count).End(xlUp).Row<br>    <br>    <SPAN style="color:#00007F">For</SPAN> i = 1 <SPAN style="color:#00007F">To</SPAN> LR<br>        <SPAN style="color:#00007F">If</SPAN> InStr(1, Range("A" & i).Value, "Total", vbTextCompare) > 0 <SPAN style="color:#00007F">Then</SPAN><br>            Range("A" & i & ":A" & Rows.Count).EntireRow.Delete<br>            <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">For</SPAN><br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN> i<br><br>    <SPAN style="color:#007F00">'Setup print area last row</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> LastRow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    LastRow = Range("A:AC").SpecialCells(xlCellTypeLastCell).Row<br>    ActiveSheet.PageSetup.PrintArea = "$A$1:$AC$" & LastRow<br>    <br><br>    <br>    <SPAN style="color:#007F00">'Setup 11x17, Print width, Landscape</SPAN><br>    Application.PrintCommunication = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">With</SPAN> ActiveSheet.PageSetup<br>        .PrintTitleRows = "$5:$7"<br>        .PrintTitleColumns = ""<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    Application.PrintCommunication = <SPAN style="color:#00007F">True</SPAN><br>    Application.PrintCommunication = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">With</SPAN> ActiveSheet.PageSetup<br>        .LeftHeader = ""<br>        .CenterHeader = ""<br>        .RightHeader = ""<br>        .LeftFooter = ""<br>        .CenterFooter = ""<br>        .RightFooter = ""<br>        .LeftMargin = Application.InchesToPoints(0.5)<br>        .RightMargin = Application.InchesToPoints(0.5)<br>        .TopMargin = Application.InchesToPoints(0.5)<br>        .BottomMargin = Application.InchesToPoints(0.5)<br>        .HeaderMargin = Application.InchesToPoints(0.5)<br>        .FooterMargin = Application.InchesToPoints(0.5)<br>        .PrintHeadings = <SPAN style="color:#00007F">False</SPAN><br>        .PrintGridlines = <SPAN style="color:#00007F">False</SPAN><br>        .PrintComments = xlPrintNoComments<br>        .PrintQuality = 1200<br>        .CenterHorizontally = <SPAN style="color:#00007F">False</SPAN><br>        .CenterVertically = <SPAN style="color:#00007F">False</SPAN><br>        .Orientation = xlLandscape<br>        .Draft = <SPAN style="color:#00007F">False</SPAN><br>        .PaperSize = xlPaper11x17<br>        .FirstPageNumber = xlAutomatic<br>        .Order = xlDownThenOver<br>        .BlackAndWhite = <SPAN style="color:#00007F">False</SPAN><br>        .Zoom = <SPAN style="color:#00007F">False</SPAN><br>        .FitToPagesWide = 1<br>        .FitToPagesTall = <SPAN style="color:#00007F">False</SPAN><br>        .PrintErrors = xlPrintErrorsDisplayed<br>        .OddAndEvenPagesHeaderFooter = <SPAN style="color:#00007F">False</SPAN><br>        .DifferentFirstPageHeaderFooter = <SPAN style="color:#00007F">False</SPAN><br>        .ScaleWithDocHeaderFooter = <SPAN style="color:#00007F">True</SPAN><br>        .AlignMarginsHeaderFooter = <SPAN style="color:#00007F">True</SPAN><br>        .EvenPage.LeftHeader.Text = ""<br>        .EvenPage.CenterHeader.Text = ""<br>        .EvenPage.RightHeader.Text = ""<br>        .EvenPage.LeftFooter.Text = ""<br>        .EvenPage.CenterFooter.Text = ""<br>        .EvenPage.RightFooter.Text = ""<br>        .FirstPage.LeftHeader.Text = ""<br>        .FirstPage.CenterHeader.Text = ""<br>        .FirstPage.RightHeader.Text = ""<br>        .FirstPage.LeftFooter.Text = ""<br>        .FirstPage.CenterFooter.Text = ""<br>        .FirstPage.RightFooter.Text = ""<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    Application.PrintCommunication = <SPAN style="color:#00007F">True</SPAN><br>    <br>    <SPAN style="color:#007F00">'FOOTER</SPAN><br>    Application.PrintCommunication = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">With</SPAN> ActiveSheet.PageSetup<br>        .PrintTitleRows = "$5:$7"<br>        .PrintTitleColumns = ""<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    Application.PrintCommunication = <SPAN style="color:#00007F">True</SPAN><br>    Application.PrintCommunication = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">With</SPAN> ActiveSheet.PageSetup<br>        .LeftHeader = ""<br>        .CenterHeader = ""<br>        .RightHeader = ""<br>        .LeftFooter = ""<br>        .CenterFooter = ""<br>        .RightFooter = "Page &P of &N"<br>        .LeftMargin = Application.InchesToPoints(0.5)<br>        .RightMargin = Application.InchesToPoints(0.5)<br>        .TopMargin = Application.InchesToPoints(0.5)<br>        .BottomMargin = Application.InchesToPoints(0.5)<br>        .HeaderMargin = Application.InchesToPoints(0.5)<br>        .FooterMargin = Application.InchesToPoints(0.5)<br>        .PrintHeadings = <SPAN style="color:#00007F">False</SPAN><br>        .PrintGridlines = <SPAN style="color:#00007F">False</SPAN><br>        .PrintComments = xlPrintSheetEnd<br>        .PrintQuality = 1200<br>        .CenterHorizontally = <SPAN style="color:#00007F">False</SPAN><br>        .CenterVertically = <SPAN style="color:#00007F">False</SPAN><br>        .Orientation = xlLandscape<br>        .Draft = <SPAN style="color:#00007F">False</SPAN><br>        .PaperSize = xlPaper11x17<br>        .FirstPageNumber = xlAutomatic<br>        .Order = xlDownThenOver<br>        .BlackAndWhite = <SPAN style="color:#00007F">False</SPAN><br>        .Zoom = <SPAN style="color:#00007F">False</SPAN><br>        .FitToPagesWide = 1<br>        .FitToPagesTall = <SPAN style="color:#00007F">False</SPAN><br>        .PrintErrors = xlPrintErrorsDisplayed<br>        .OddAndEvenPagesHeaderFooter = <SPAN style="color:#00007F">False</SPAN><br>        .DifferentFirstPageHeaderFooter = <SPAN style="color:#00007F">False</SPAN><br>        .ScaleWithDocHeaderFooter = <SPAN style="color:#00007F">True</SPAN><br>        .AlignMarginsHeaderFooter = <SPAN style="color:#00007F">True</SPAN><br>        .EvenPage.LeftHeader.Text = ""<br>        .EvenPage.CenterHeader.Text = ""<br>        .EvenPage.RightHeader.Text = ""<br>        .EvenPage.LeftFooter.Text = ""<br>        .EvenPage.CenterFooter.Text = ""<br>        .EvenPage.RightFooter.Text = ""<br>        .FirstPage.LeftHeader.Text = ""<br>        .FirstPage.CenterHeader.Text = ""<br>        .FirstPage.RightHeader.Text = ""<br>        .FirstPage.LeftFooter.Text = ""<br>        .FirstPage.CenterFooter.Text = ""<br>        .FirstPage.RightFooter.Text = ""<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    Application.PrintCommunication = <SPAN style="color:#00007F">True</SPAN><br><br>    <SPAN style="color:#00007F">With</SPAN> ActiveSheet.PageSetup<br>        .PrintTitleRows = "$3:$5"<br>        .PrintTitleColumns = ""<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br><br></FONT>

 

[wangh3]

Sorry did not mean to put my whole code in. It's really these 2 pieces below that are causing my problem.

'Delete Total Rows
LR = Range("A" & Rows.Count).End(xlUp).Row

For i = 1 To LR
If InStr(1, Range("A" & i).Value, "Total", vbTextCompare) > 0 Then
Range("A" & i & ":A" & Rows.Count).EntireRow.Delete
Exit For
End If
Next i

'Setup print area last row
Dim LastRow As Long
LastRow = Range("A:AC").SpecialCells(xlCellTypeLastCell).Row
ActiveSheet.PageSetup.PrintArea = "$A$1:$AC$" & LastRow

 

[wangh3]

bump

 

[wangh3]

bump bump

 

[wangh3]

sigh bump

 

[wangh3]

bump