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

 

[MikeVol]

@feni1388,
So what is the text, the cell address is written in the message in this line:

MsgBox Cells(k, 1).Address <----- Insert this line

from the 3rd post from the user @jolivanes??? Can you answer?

 

[feni1388]

Read the 3rd and 4th line in Post #3 again.

Sorry, I misunderstood it.
There's nothing shown in the message box or there's no message box actually.

When I run the macro line by line and hover my cursor...... on the Rows.Count part it shows 1048576.
k = Cells(Rows.Count, 1).End(xlUp).Row

then on this part, it shows k=nothing
MsgBox Cells(k, 1).Address

 

[jolivanes]

Dim k As Range    '<---- This should be Long, not Range
On Error Resume Next    '<---- Delete this line
k = Cells(Rows.Count, 1).End(xlUp).Row

 

[feni1388]

Dim k As Range    '<---- This should be Long, not Range
On Error Resume Next    '<---- Delete this line
k = Cells(Rows.Count, 1).End(xlUp).Row

The message box showed $A$145

 

[jolivanes]

Do you get white space with this?

Sub Check_It()
With ActiveSheet
    .PageSetup.PrintArea = "A1:S" & .Cells(.Rows.Count, 1).End(xlUp).Row
    .PrintOut , , , -1
End With
End Sub

 

[feni1388]

Yes, I still get white space with this

 

[jolivanes]

Now tell us the whole story.
You set the print area from A1 to Column S and the last used cell in Column A.
How many columns and rows without data are in this range?

 

[MikeVol]

@feni1388,
Better yet, attach your original example file without confidential data (Replace with other data) to this topic! We are now playing the game “Guess what’s in my left hand”. We are waiting for an example file from you

 

[feni1388]

Below is my sample file.
(it seems that there's no way that I can attach the whole file, so I only attached the sheet that need to be printed)

Note: the last used cell is column G. I modified the code so that it will count from column G. but still the same result

data sample.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1									Trust PO		20240513_Trust_PO.xlsx							New:	20240513_Trust_PO_102418
2	Order No	Date	Item	Packs	Box	Price	Total	Total per item	Zip code	Addess	Company's name	Store	Phone no.	memo	Inv	Total	Box	Existing:	20240513_Trust_PO.xlsx
3	HEC81820	20240513	GL77	30	1	246	7,380	7,380	340-0194	A town	Trust		0480-31-8888	check the price	Other	7,380	1
4			Total		1		7,380
5	HEC81819	20240513	GH77	50	1	135	6,750	6,750	340-0194	A town	Trust		0480-31-8888	check the price	Other	6,750	1
6			Total		1		6,750
7							14,130
8
9
10
11
12
13
14
print

Cell Formulas
Range	Formula
K1	K1	=IF(Sheet1!D1="","",Sheet1!D1)

 

[jolivanes]

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