vba print area

[kelvin_9]

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?
it seems my code below will goto row 250 every time and i will print "blank sheet" out

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

thanks for everyone.

 

[Joe4]

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:

LastRow = Cells(Rows.Count, "A").End(xlUp).Row

 

[kelvin_9]

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:

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,

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

 

[MARK858]

Maybe...

Sub macro1B()
    ActiveSheet.PageSetup.PrintArea = Range("A9:I" & Range("B" & Rows.Count).End(xlUp).Row).Address
End Sub

 

[kelvin_9]

Maybe...

Sub macro1B()
    ActiveSheet.PageSetup.PrintArea = Range("A9:I" & Range("B" & Rows.Count).End(xlUp).Row).Address
End Sub

it's still hit to row 250

 

[MARK858]

Then what have you got in row 250, in particular column B? Do you have formulas returning ""?

 

[kelvin_9]

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.

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

 

[MARK858]

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.

 

[kelvin_9]

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

 

[MARK858]

Try...

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.