VBA Range.Offset Error 1004 Range beyond scope of sheet Dynamic Range

January 11, 2023

When the schedule is new and empty, the offset moves beyond the scope of the sheet and gives error 1004.
Needs a way to dynamically move up from the initially selected cell without error.
Some production lines drastically jump around the schedule which is why the offset up needs to be so great.

Option Explicit
Sub Print_Line_3()

    Dim lRow As Range
   
Application.ScreenUpdating = False
    With Sheets("Line 3")
        Set lRow = .Range("G1024").End(xlUp).Offset(-6, -6).Resize(24, 14)
        lRow.PrintOut
    End With
Application.ScreenUpdating = True

End Sub

' Starts from an arbitrary point then looks up to the last filled cell in that column.
' Moves from the selected cell up 6 then left 6 spots.
' Creates a selected range from previous cell to create a range to printout from.

>Solution :

Check the .Row first:

With Sheets("Line 3").Range("G1024").End(xlUp)
    If .Row > 6 Then
        Set lRow = .Offset(-6, -6).Resize(24, 14)
        lRow.PrintOut
    End If
End With