# Excel "subscript out of range" error
**URL:** https://boards.straightdope.com/t/excel-subscript-out-of-range-error/634324
**Category:** Factual Questions
**Created:** [September 10, 2012, 8:05pm UTC](https://boards.straightdope.com/t/excel-subscript-out-of-range-error/634324 "2012-09-10T20:05:21Z")
**Posts on this page:** 5
**Page:** 1
### Author:  [@Don\_t\_Call\_Me\_Shirley](https://boards.straightdope.com/u/Don_t_Call_Me_Shirley)
#### Post date: [September 10, 2012, 8:05pm UTC](https://boards.straightdope.com/t/excel-subscript-out-of-range-error/634324/1 "2012-09-10T20:05:21Z")
I have some VBA macros that I have run every day for a year and a half with not a single error. Today I started getting the error “9 Subscript out of range” every time it gets to a line removing duplicates:
ActiveSheet.Range("$A$1:$J" & last).RemoveDuplicates Columns:=Array(5, 6), Header:=xlYes
If I take the array out of there and just list one column it works. If I remove duplicates manually it works. If I record a macro and run it it works. But it won’t work inside the macro that has been running fine up until now.
I have one clue: I got a csv file from a vendor and opened it and that’s when the problem started. I opened the same file on another computer and it started having the problem too. So I’m thinking that might have something to do with it.
---
### Author:  [@mcgato](https://boards.straightdope.com/u/mcgato)
#### Post date: [September 10, 2012, 9:19pm UTC](https://boards.straightdope.com/t/excel-subscript-out-of-range-error/634324/2 "2012-09-10T21:19:10Z")
Since nobody has answered, I’ll give a somewhat uninformed response. I’ve had issues in the past with setting a range dynamically like you are doing with the “&last” thing. To get around it, I defined the range in a separate line and then used that defined range inside the parentheses. I don’t remember what the actual command is, but it is something like:
Set UseRange="$A$1:$J"&last
I may have had to define UseRange to be of type Range up top.
Unless you recently changed to a different version of Excel. A bunch of my macros had stupid little errors after my company upgraded to a newer (but still about 5 years out of date) version of Excel.
---
### Author:  [@TroutMan](https://boards.straightdope.com/u/TroutMan)
#### Post date: [September 10, 2012, 9:39pm UTC](https://boards.straightdope.com/t/excel-subscript-out-of-range-error/634324/3 "2012-09-10T21:39:27Z")
What is the value of ‘last’ when you get the error?
---
### Author:  [@Don\_t\_Call\_Me\_Shirley](https://boards.straightdope.com/u/Don_t_Call_Me_Shirley)
#### Post date: [September 10, 2012, 9:53pm UTC](https://boards.straightdope.com/t/excel-subscript-out-of-range-error/634324/4 "2012-09-10T21:53:01Z")
> [@TroutMan](#):
>
> What is the value of ‘last’ when you get the error?
1. And I took out the “& last” and replaced it with 88, and that didn’t work. I also set the array up as a variable and tried that.
I also ran Repair on Office to try to reset everything to the defaults, and that didn’t work either.
The only thing that works is removing the array.
---
### Author:  [@Don\_t\_Call\_Me\_Shirley](https://boards.straightdope.com/u/Don_t_Call_Me_Shirley)
#### Post date: [September 10, 2012, 10:44pm UTC](https://boards.straightdope.com/t/excel-subscript-out-of-range-error/634324/5 "2012-09-10T22:44:55Z")
I may have found a solution, but I’m not holding my breath until I see if it still works tomorrow.
Dim ColNames(0 To 1) As Variant
```
ColNames(0) = 4
ColNames(1) = 7
ActiveSheet.Range("$A$1:$G$" & last).RemoveDuplicates Columns:=(ColNames), Header _
:=xlYes
```
Seems ridiculously complicated to fix something that worked fine yesterday. Also, at first it caused another “subscript out of range” error later in the macro, in a ridiculous place that should never have that error. But I moved the remove duplicates code to after the new error, and in that order everything works. Hopefully someone will be able to explain what is going on.