VBA Error - Subscript out of range - Runtime Error 9

Question

Dim LastRow As Long

With Workbooks("SampleBook").Sheets("SampleSheet")
    LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

I have been running this code for over a month now. I had no issues. Suddenly, for the past two days, it's throwing the subscript out of range - run time error(9).

Not sure what's going on!

Your help is appreciated :)

Thank you!

Did you rename the workbook or worksheet? Try something like, Dim wb as workbook Dim ws as Worksheet Set wb = Workbooks("SampleBook") Set ws = wb.Sheets("SampleSheet") as see if that runs. — OpiesDad
– OpiesDad, Commented Jun 21, 2018 at 18:53

NM24 · Accepted Answer · 2018-06-21 18:55:50Z

1

Figured it out!! I had enabled the show file extension option in folder options. I needed to add the extension to my workbook name. The following modification worked

With Workbooks("SampleBook.xlsm").Sheets("SampleSheet") LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row End With

answered Jun 21, 2018 at 18:55

NM24

971 silver badge9 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user4039065 Over a year ago

Wasn't there a question several days ago about one computer in a network of many that required the .xlsx extension when naming a workbook in the workbooks collection? Seems this would be the answer.

Collectives™ on Stack Overflow

VBA Error - Subscript out of range - Runtime Error 9

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related