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

0 votes

The offset moves outside the boundaries of the sheet when the schedule is fresh and empty, resulting in error 1004. need a method for correctly moving up from the initially chosen cell in a dynamic manner. Because some manufacturing lines wildly diverge from the schedule, a large offset is required.

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.
Jan 12 in Others by Kithuzzz
• 27,740 points
44 views

1 answer to this question.

0 votes

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
answered Jan 12 by narikkadan
• 51,240 points

Related Questions In Others

0 votes
1 answer

VBA to protect and unprotect in given range in sheet

It needs a minor tweak to become ...READ MORE

answered Nov 24, 2022 in Others by Kithuzzz
• 27,740 points
163 views
0 votes
1 answer

How to increment the Range of a For Each loop - Excel VBA

Your formula seems to sum 1 single ...READ MORE

answered Jan 7 in Others by narikkadan
• 51,240 points
58 views
0 votes
1 answer

Flutter Error: MediaQuery.of() called with a context that does not contain a MediaQuery.

Hi@akhtar, You need a MaterialApp or a WidgetsApp around your widget. They ...READ MORE

answered Aug 27, 2020 in Others by MD
• 95,420 points
4,178 views
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
522 views
0 votes
1 answer

How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
2,382 views
0 votes
1 answer

Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE

answered Feb 9, 2022 in Others by gaurav
• 22,940 points
110 views
0 votes
2 answers

How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,640 points
274 views
0 votes
1 answer

VBA code to select only a table. I am getting a Run-time error '1004'; Method 'Range' of object'_Global' failed

No copy/paste, just direct assignment use.Value Sub Final_Report() ...READ MORE

answered Jan 13 in Others by narikkadan
• 51,240 points
64 views
0 votes
1 answer

Excel VBA - Subscript Out of Range Error (Run Time: Error 9)

Set rangePartner = Range(Partner.Cells(2, 2), Partner.Cells(2, 2).End(xlDown)) Needs ...READ MORE

answered Jan 21 in Others by narikkadan
• 51,240 points
33 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP