Dynamic print area for rows and columns

picklefactory

Well-known Member
Joined
Jan 28, 2005
Messages
506
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEFGHIJK
110-Jul-2013/07/2020 00:00:0016/07/2020 00:00:0017/07/2020 00:00:0020/07/2020 00:00:0030/07/2020 00:00:00Total
2DivisionPart NoDescription13/07/2016/07/2017/07/2020/07/2030/07/20Total  
3Customer 1Part 1#N/A24.0070.0094.00
4Part 2#N/A33.0033.00
5Part 3#N/A4.004.00
6Customer 2Part 4#N/A40.0040.00
7Part 5#N/A40.0040.00
8Part 6#N/A40.0040.00
9Customer 3Part 7#N/A90.0044.00134.00
10Part 8#N/A42.0026.0068.00
11Customer 4Part 3#N/A50.0050.00
12Total 132.0024.004.00240.00103.00503.00
13 
DATA IN
Cell Formulas
RangeFormula
A1A1=TODAY()
D2:K2D2=IF(D1="Total","Total",IF(D1="","",INT(D1)))
C3:C13C3=IF(B3="","",VLOOKUP(B3,LISTS!$A$1:$B$27,2,FALSE))
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
This might be what you're looking for:
VBA Code:
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
 
Upvote 0
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.
 
Upvote 0
Yep.... as expected, works perfectly.
Thanks again, you guys are a Godsend to us amateurs
:)
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,916
Members
448,533
Latest member
thietbibeboiwasaco

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