VBA macro to set up dynamic print area

[jb12849]

I am trying to create a macro that sets a dynamic print area. There is a defined number of columns but an undefined number of rows. The print area should find the row with the last data point between a range of columns. I've attached a file to show what I mean. The columns that will have data (numbers and text) are C to K. In the file, the last data point is in D46, so the print area should be A1:K46.

How can I create a macro that sets the print area from A1 to K(?), where (?) is the last row with data in the columns C to K?

I'm no VBA expert but this would be very helpful. Any thoughts?

Thanks :D

[mdmackillop]

Sub SetPA()
Rw = Range("C:K").Find("*", [C1], , xlPart, xlByRows, xlPrevious).Row
ActiveSheet.PageSetup.PrintArea = "$A$1:$K$" & Rw
End Sub

[jolivanes]

https://www.excelforum.com/excel-programming-vba-macros/1201219-macro-to-set-dynamic-print-area.html

[jb12849]

The formula works if the cells below don't have any formulas in them. How can the "*" be replaced with something that will only count data as the formulas that yield actual values?

[jb12849]

Basically the formulas are copied down all throughout the sheet, but they stop yielding values after a certain point. How do we change "*" so that it only includes data points that came from the formulas?

[SamT]

Find(What:="=", Lookin:=xlFormulas, ...

[mdmackillop]

For the future, the purpose of a sample file is to represent your actual workbook, otherwise solutions may not fit.
I'm not clear on posts 4 & 5. You want the last cell derived from a formula and to exclude any cells with Constants?

Assuming that is incorrect

Sub SetPA()
Rw = Range("C:K").Find("*", [C1], xlValues, xlPart, xlByRows, xlPrevious).Row
ActiveSheet.PageSetup.PrintArea = "$A$1:$K$" & Rw
MsgBox Range("Print_Area").Address
End Sub