Dynamic print area for rows and columns

[picklefactory]

Hi Folks, hoping for some advice... as usual.
I'm trying/failing to get a dynamic print area to work for me. I've tried using name manager and the offset/ counta formula, but I must be doing something wrong as it keeps shortening the area. I've also tried a VBA version using activesheet and used range, but as I have a heap of formulas in row 2 and also col B.... it selects too much.
OK, what I have is a sheet that imports a load of data via VBA and basically just reformats it into a user friendly format for display purposes, it can change size by way of both rows and columns. I have a single row that contains fixed formulae covering the maximum extent of columns I would ever need, and also further fixed formulae in column C going as far as row 48, but those cells will show blank if there is no value to be currently displayed. When I tried the VBA UsedRange function, those blank cells with formulae kept it defaulting to the maximum number of columns.
Is there a VBA method that can set Print Area from cell A1 to the furthest cell address that contains a value (In this current instance cell I12)?
Sample sheet enclosed
Thanks

JCB OPEN ORDERS.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	10-Jul-20			13/07/2020 00:00:00	16/07/2020 00:00:00	17/07/2020 00:00:00	20/07/2020 00:00:00	30/07/2020 00:00:00	Total
2	Division	Part No	Description	13/07/20	16/07/20	17/07/20	20/07/20	30/07/20	Total
3	Customer 1	Part 1	#N/A		24.00			70.00	94.00
4		Part 2	#N/A					33.00	33.00
5		Part 3	#N/A			4.00			4.00
6	Customer 2	Part 4	#N/A				40.00		40.00
7		Part 5	#N/A				40.00		40.00
8		Part 6	#N/A				40.00		40.00
9	Customer 3	Part 7	#N/A	90.00			44.00		134.00
10		Part 8	#N/A	42.00			26.00		68.00
11	Customer 4	Part 3	#N/A				50.00		50.00
12	Total			132.00	24.00	4.00	240.00	103.00	503.00
13
DATA IN

Cell Formulas
Range	Formula
A1	A1	=TODAY()
D2:K2	D2	=IF(D1="Total","Total",IF(D1="","",INT(D1)))
C3:C13	C3	=IF(B3="","",VLOOKUP(B3,LISTS!$A$1:$B$27,2,FALSE))

 

[GWteB]

This might be what you're looking for:

Sub DynamicPrintArea()
    
    Dim lRow    As Long
    Dim lCol    As Long
    Dim sAddr   As String
    
    With ThisWorkbook.Worksheets("DATA IN")
        On Error Resume Next
        lRow = .Cells.Find(What:="*", After:=.Range("A1"), Lookat:=xlPart, LookIn:=xlValues, _
                           SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Row
        lCol = .Cells.Find(What:="*", After:=.Range("A1"), Lookat:=xlPart, LookIn:=xlValues, _
                           SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, MatchCase:=False).Column
        sAddr = .Cells(lRow, lCol).Address(False, False)
        If Err.Number > 0 Then
            sAddr = .Cells(1).Address(False, False)
            Err.Clear
        End If
        On Error GoTo 0
        .PageSetup.PrintArea = "A1:" & sAddr
    End With
End Sub

 

[picklefactory]

GWteB
Thank you very much indeed. Wasn't my best idea to post that on a Friday afternoon when I wouldn't have time to test, but just tried it from home on a limited trial and worked perfectly, I'll check it out fully on Monday and post back, but looks perfect so far.
Thanks again for your time and trouble.

 

[GWteB]

You are welcome and thanks for the feedback.

 

[picklefactory]

Yep.... as expected, works perfectly.
Thanks again, you guys are a Godsend to us amateurs