Set up print area and avoid printing blank pages (VBA)

[feni1388]

Hello...

I'm using the vba code below to set up print area and avoid printing blank pages, but it doesn't seem to work.
Can someone please help?

Column A to S is fixed but the row can sometimes short or long. When it's short, all the blank pages got printed too.

Dim rng As Range
Dim k As Range
On Error Resume Next
k = Cells(Rows.Count, 1).End(xlUp).Row
Set rng = ActiveSheet.Range("A1:S" & k)

With ActiveSheet.PageSetup
.PrintArea = rng.Address
.Orientation = xlLandscape
End With

Application.ScreenUpdating = True

 

[feni1388]

No formulas in Column G?

What do you see with this?

Sub Check_It_B()
With ActiveSheet
    With .PageSetup
        .PrintArea = "A1:S" & .Cells(.Rows.Count, 7).End(xlUp).Row
        .Orientation = xlLandscape
        .Zoom = False
        .FitToPagesTall = 1
        .FitToPagesWide = 1
    End With
.PrintOut , , , -1
End With
End Sub

Perhaps my excel version is older so it stopped at the printarea part.
So I modified it like below

          Dim rng As Range
          Dim k As Long

          k = Cells(Rows.Count, 7).End(xlUp).Row
          Set rng = ActiveSheet.Range("A1:N" & k)

         
           With ActiveSheet
       With .PageSetup
        '.PrintArea = "A1:N" & .Cells(.Rows.Count, 7).End(xlUp).Row --> it stopped here with error 438 (not supported)
        .Orientation = xlLandscape
        .Zoom = False
        .FitToPagesTall = 1
        .FitToPagesWide = 1
    End With
.PrintOut , , , -1
End With

The result was it became so small. It seems that it got zoomed to 16%

 

[jolivanes]

So you have to set your page breaks to what you want to see on each page.
I don't know how many rows your actual sheet has but if you set your print area as above and have vertical page breaks set it should come out OK
Mind you, each sheet will have only nine rows, blank space to the end and then the next sheet with the same.
Is this the blank spaces you're talking about?

 

[feni1388]

Sorry, I got so confused.
The blank spaces that I meant is that no matter what code I use it keeps having 5 pages (1 page with data in it and 4 blank pages)

Just in case I clear out all print area.
Then set up the print area just like your code.
But when I ran the code, it seems that it doesn't recognise the last row in column G (as it keeps showing 144)
Just in case, I also moved the title to cell G1, so that there's no blank there, but still the same.

 

[MikeVol]

From my #2 post with slight modification

    Dim LR          As Long

    With ActiveSheet
        LR = .Range("G" & .Rows.Count).End(xlUp).Row
        .PageSetup.PrintArea = .Range(.Cells(1, 1), .Cells(LR, "S")).Address
        .PageSetup.Orientation = xlLandscape
        .PageSetup.Zoom = False
        .PageSetup.FitToPagesTall = 1
        .PageSetup.FitToPagesWide = 1
        .PrintOut , , , -1
    End With

Works for me. Good luck.

 

[jolivanes]

You say it keeps showing 144 as last cell in Column G.
Do you have formulas in that column?
If in your example from Post #19 you select entire rows from 1 row below the last cell in column G (row 10) to row 200 and delete these rows, what does it show then?
Use this to find the last used cell and let us know what it gives you.

lr = Cells.Find("*", ,xlValues , , xlByRows, xlPrevious).Row

@MikeVol
Re your Post #24.
See what the OP mentioned at the end of Post #21.

 

[jolivanes]

If the width of your printing range exceeds the width of your sheet in Landscape, you'll get 2 pages or more to print.
This sets vertical page breaks every 6 columns. Experiment with it and change as desired. Change the step amount to 1 less than the i amount.
If you're close but not quite there where you want to be, experiment with the Zoom setting. Go down 5 numbers at the time.
You're not wasting any paper because it is set to PrintPreview.

Sub Check_It_C()
Dim sh1 As Worksheet
Dim lr As Long, i As Long
Dim rng As Range
Set sh1 = Worksheets("Sheet1")    '<---- Change as required
lr = sh1.Cells.Find("*", , xlValues, , xlByRows, xlPrevious).Row
Set rng = sh1.Range("A1:S" & lr)
With sh1
    .ResetAllPageBreaks
    For i = 7 To 19 Step 6    '<---- See above explanation
        .Columns(i).PageBreak = xlPageBreakManual
    Next i
End With
    With sh1.PageSetup
        .PrintArea = rng.Address
        .Orientation = xlLandscape
        .Zoom = 100    '<---- See above explanation
    End With
sh1.PrintOut , , , -1
End Sub

 

[feni1388]

If the width of your printing range exceeds the width of your sheet in Landscape, you'll get 2 pages or more to print.
This sets vertical page breaks every 6 columns. Experiment with it and change as desired. Change the step amount to 1 less than the i amount.
If you're close but not quite there where you want to be, experiment with the Zoom setting. Go down 5 numbers at the time.
You're not wasting any paper because it is set to PrintPreview.

Sub Check_It_C()
Dim sh1 As Worksheet
Dim lr As Long, i As Long
Dim rng As Range
Set sh1 = Worksheets("Sheet1")    '<---- Change as required
lr = sh1.Cells.Find("*", , xlValues, , xlByRows, xlPrevious).Row
Set rng = sh1.Range("A1:S" & lr)
With sh1
    .ResetAllPageBreaks
    For i = 7 To 19 Step 6    '<---- See above explanation
        .Columns(i).PageBreak = xlPageBreakManual
    Next i
End With
    With sh1.PageSetup
        .PrintArea = rng.Address
        .Orientation = xlLandscape
        .Zoom = 100    '<---- See above explanation
    End With
sh1.PrintOut , , , -1
End Sub

Wow....... It works..... it really works.....
Like you said I adjusted the step amount and also the zoom setting and it works....................
Thank you so much for your help.
I really appreciate it.

@MikeVol
Thank you for your help too....

 

[jolivanes]

Good to hear and Good Luck.