Dynamic print range, stopping at the last visible row

[johols]

Goal
To have a dynamic print range, stopping at the last visible row.

Column C
Contains non visible formulas.
The formulas start at row 17 and continue to row 200.
Sometimes these rows contains visible data and sometimes not.

Column D-L
Contains sometimes free text.

Problem
Can’t figure out how to change print area to last visible row.
Want to print out everything until the last visible row, even rows with no visible data.
But after last visible row, nothing should come out.
Script below working fine, except it displays all empty rows (which contains formulas).
Resulting all print jobs will have empty pages at the bottom with no visible data.


Using this print area script
Sub print area()

Dim LastRow As Long
LastRow = Range("C:L").SpecialCells(xlCellTypeLastCell).Row
ActiveSheet.PageSetup.printarea = "$C$1:$L$" & LastRow
End Sub


Help
Can anyone help me obtain the goal ?

 

[Mohammad Basem]

Welcome to the forum!

Try this,

[FONT=Consolas][SIZE=2][COLOR=Navy]LastRow = Range("C:L").Find(What:="*", SearchDirection:=xlPrevious, _
    LookIn:=xlValues, LookAt:=xlWhole).Row[/COLOR][/SIZE][/FONT]

 

[johols]

Welcome to the forum!

Try this,

[FONT=Consolas][SIZE=2][COLOR=navy]LastRow = Range("C:L").Find(What:="*", SearchDirection:=xlPrevious, _
    LookIn:=xlValues, LookAt:=xlWhole).Row[/COLOR][/SIZE][/FONT]

Thank you, just tried your code and it works like a charm, very good!

Another problem is that the footer doesn’t have enough space so my text don’t fit.
Is it possible to add text at the last page just above the footer on every printout, or if that doesn’t work add text just above the last row in a specific column.

Example text: </SPAN>
“thank you for your order!” </SPAN>
last in column C in every printout ?</SPAN>

 

[Mohammad Basem]

Al-hamdullah.

Is it possible to add text at the last page just above the footer on every printout, or if that doesn’t work add text just above the last row in a specific column.

It is possible, but this should be done on the sheet before printing and not during printing, i.e. insert the text below the last row in the specified column before start printing then delete afterward.

 

[jdppep]

I am trying to do the same thing across my workbook. I am relatively new to VBA coding.

Columns A-DA
Contains non-visible data. The formulas start at row 10 and continue to row 1000.
Sometimes these rows contain visible data and/or conditionally formatting (blank if empty) and sometimes not.I have tried the following to no avail:

Sub PrintArea()


Dim LastRow As Long
LastRow = Range("A:DD").Find(What:="*", SearchDirection:=xlPrevious, _
LookIn:=xlValues, LookAt:=xlWhole).Row
ActiveSheet.PageSetup.PrintArea = "$A$1:$DD$" & LastRow


End Sub

Please help! Thank you!