Excel "subscript out of range" error

Factual Questions
1

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.

2

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.

3

What is the value of ‘last’ when you get the error?

4
  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.

5

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.