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, 2023 in Others by Kithuzzz
• 38,000 points
845 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, 2023 by narikkadan
• 63,600 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
• 38,000 points
2,600 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, 2023 in Others by narikkadan
• 63,600 points
2,733 views
0 votes
1 answer

How do I copy a specific range of cells after I use AutoFilter in VBA?

Set the range of filtered data Set Rng ...READ MORE

answered Feb 2, 2023 in Others by narikkadan
• 63,600 points
864 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
1,215 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
3,668 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
• 23,260 points
913 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,690 points
1,057 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, 2023 in Others by narikkadan
• 63,600 points
1,121 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, 2023 in Others by narikkadan
• 63,600 points
1,016 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