Setting Print Area

[doumob]

I am trying to set up a record sheet that I need to be printable.

The sheet has information set in columns A-C and then records of issue in row 7 of further columns (plus other data in various rows)

I am looking to have a way to set the printing area to be columns A-S originally, but when there is data in columns beyond S the print area changes to columns A-C plus the last 16 columns with data in on row 7.
I have tried solutions around print areas, print titles etc, but getting very lost and not getting to a working solution. Is there a VBA or other solution to this so its user friendly?

 

[Logit]

Untested here. See if the following works for you :

Sub SetDynamicPrintArea()
    Dim ws As Worksheet
    Dim lastCol As Long
    Dim printArea As String
    Dim additionalColsStart As Long
    Dim additionalColsEnd As Long

    ' Set the worksheet (use ActiveSheet if this macro will apply to the active sheet)
    Set ws = ActiveSheet

    ' Find the last column with data in row 7
    lastCol = ws.Cells(7, ws.Columns.Count).End(xlToLeft).Column

    ' Check if the last column is within or beyond column S
    If lastCol <= 19 Then ' Column S is the 19th column
        ' Set the print area to columns A-S
        printArea = ws.Range("A1:S" & ws.Rows.Count).Address
    Else
        ' Set the print area to columns A-C and the last 16 columns with data in row 7
        additionalColsStart = lastCol - 15
        additionalColsEnd = lastCol
        printArea = ws.Range("A1:C" & ws.Rows.Count).Address & "," & _
                    ws.Range(ws.Cells(1, additionalColsStart), ws.Cells(ws.Rows.Count, additionalColsEnd)).Address
    End If

    ' Set the print area
    ws.PageSetup.PrintArea = printArea

    ' Optional: Notify the user
    MsgBox "Print area set to: " & printArea, vbInformation
End Sub

 

[doumob]

Thank you, works a treat

 

[Logit]

You are welcome.