Vba Blank Pages After Print Area

[kmmsquared]

Hi,
I am using the following code to set print ranges. I use this same exact code as part of another macro and it formats the pages correctly (it only uses the number of pages tall that is necessary). In a separate workbook, the columns format correctly in width, but there are extra pages added after the last row of data. For example when the spreadsheet print range should be 55 pages long, output is 290 pages--just blank pages (except for the repeated row 1) after page 55.

How can I fix this? Any help would be appreciated! Thanks!

Sub PageSetup()
 
Dim LastRow As Long
Dim LastColumn As Long
With Sheets("B")
   LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
   LastColumn = Application.WorksheetFunction.Match("E", Range("1:1"), 0)
 
Sheets("B").Select
Sheets("B").Activate
With ActiveSheet.PageSetup
.FirstPageNumber = 1
.PrintTitleRows = "$1:$1"
.PaperSize = xlPaperLegal
.Orientation = xlLandscape
.Zoom = False
.View = xlPageBreakPreview
[COLOR=red].FitToPagesWide = 1
.FitToPagesTall = 1000
[/COLOR].PrintArea = "A1:" & LastColumn & LastRow
 
End With
End Sub

 

[baitmaster]

try .FitToPagesTall = FALSE

 

[kmmsquared]

Still 290 pages and blank after page 55.

 

[Datsmart]

As written, your code will fail since it is missing an "End With" statement.

Your "LastColumn" variable is being set using the column number where "E" is found in the first row.
Does the workbook where this adds too many pages have an "E" in row 1?

 

[baitmaster]

then you're probably suffering from the fact you've already told Excel the file is 1000 pages tall, or something like that. Its a common Excel problem, that it doesn't reset the last cell in the sheet once it has been used

As a one-off, delete all rows from your last row to Excel's last row, then select cell A1, and save the file. You'll notice the vertical scroll bar should change size during file save

If this issue is being generated by your other code, then you'll need to reset the sheet size programattically too. Search for reducing file sizes, resetting last cell, something like that

 

[kmmsquared]

@Datsmart--there is a "With End" statement, I just forgot to copy it over.

There is an "E" in row 1.

@baitmaster

I may have to try your solution...nothing else is working!

 

[kmmsquared]

I reset the last cell and finally got it to work. Thanks!!!!

 

[CarlStephens]

I reset the last cell and finally got it to work. Thanks!!!!

Would you mind if I asked what you added to the code to fix this? I have the same issue......I have three pages that I want to print to pdf which is good on the excel sheet, however, when it prints, I get an extra 5 blank pages, and do not know what to put in the code to stop it. Thank you.