vba print area

kelvin_9

Active Member
Joined
Mar 6, 2015
Messages
444
Office Version
  1. 2019
hello all,
i have a worksheet with column A-I, row 1-250.
however i'm not necessary to use every row every time.

the question is how can i simply set the print area automatically to last row thus i can use less paper?:ROFLMAO:
it seems my code below will goto row 250 every time and i will print "blank sheet" out:eek:

Code:
Dim LastRow As Long
    LastRow = Range("A:I").SpecialCells(xlCellTypeLastCell).Row
    ActiveSheet.PageSetup.PrintArea = "$A$1:$I$" & LastRow

thanks for everyone.
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Have you formatted down to row 250?
If so, it thinks that is where the end is.

Assuming that column A is always populated for any row that has data, maybe try calculating LastRow like this instead:
Code:
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
 
Upvote 0
Have you formatted down to row 250?
If so, it thinks that is where the end is.

Assuming that column A is always populated for any row that has data, maybe try calculating LastRow like this instead:
Code:
LastRow = Cells(Rows.Count, "A").End(xlUp).Row

thanks Joe
seems not working what i want.
i posted a picture here and my worksheet like this, what if i want to print A9:I14 in 1sheet, is it possible or any idea that make my request success

thanks,

Code:
Sub macro1()

Dim LastRow As Long
    'LastRow = Range("A:I").SpecialCells(xlCellTypeLastCell).Row
    LastRow = Cells(Rows.Count, "A").End(xlUp).Row
    ActiveSheet.PageSetup.PrintArea = "$A$1:$I$" & LastRow
End Sub


b328229978
 
Upvote 0
Maybe...

Code:
Sub macro1B()
    ActiveSheet.PageSetup.PrintArea = Range("A9:I" & Range("B" & Rows.Count).End(xlUp).Row).Address
End Sub
 
Last edited:
Upvote 0
Then what have you got in row 250, in particular column B? Do you have formulas returning ""?
 
Upvote 0
Then what have you got in row 250, in particular column B? Do you have formulas returning ""?

i have a formula in column B, D, E, F, G & G to return what i need in database sheet.
before i try your code, i have this to turn all formulas no matter on blank/non-blank cell to value.

Code:
Sub Macro18()    Cells.Select
    Range("A9").Activate
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("A9").Select
End Sub

thanks Mark
 
Upvote 0
No you don't need to change anything at this stage, just answer the question as to what is in B250? is it a formula returning "" once you have answered that then we can (depending on your answer) amend the code.
 
Last edited:
Upvote 0
No you don't need to change anything at this stage, just answer the question as to what is in B250? is it a formula returning "" once you have answered that then we can (depending on your answer) amend the code.

yes, a formula return "" iferror in the original

dff01e3fc5
 
Last edited:
Upvote 0
Try...

Code:
Sub macro1B()
    ActiveSheet.PageSetup.PrintArea = Range("A9:I" & Columns(2).Find("*", , xlValues, , xlByRows, xlPrevious).Row).Address
End Sub

Untested and expect typos as I am on my phone.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,553
Messages
6,114,279
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top