Application-defined or object-define error when using Range

Question

The 2nd line of code below is throwing the abovementioned error:

Dim rng As String
rng = Range(Cells(2, 1), Cells(4000, 302)).Address

*302nd column is fixed and is the end column of the selection that I must copy.

What I find odd is replacing 302 with 255 or below gets rid of the error and enables my program to work.

Another thing I do not understand is; using the immediate window ---- having 255 on the column parameter prints $A$2:$IU$4000, as it should. However, replacing it with 256 throws $2:$4000 whereas I believe it should be $A$2:$IV$4000. Any number above results to the same error.

What am I missing here?

If you're using Excel<=2003, It only supports column up to IV (which should be 256), so 302 would be out-of-range. — Passerby
– Passerby, Commented Oct 12, 2012 at 5:30
Are you using xl2003 which only has 256 columns? Hence the code works fine with 255 or less, but 302 blows the column limit — brettdj
– brettdj, Commented Oct 12, 2012 at 5:31
And since IV(256) is the last column, selecting $A$2:$IV$4000 is actually seleting $2:$4000 (full row selection). — Passerby
– Passerby, Commented Oct 12, 2012 at 5:32
@Passerby and brettdj, you guys were both right. Thanks! I now feel silly missing something that seems so basic — user1737389
– user1737389, Commented Oct 12, 2012 at 5:39
Maybe one of the comments (@Passerby?) should be posted as an answer? — Olle Sjögren
– Olle Sjögren, Commented Oct 12, 2012 at 8:04

Passerby · Accepted Answer · 2012-10-12 08:24:34Z

1

Alright, extending from comment:

For your first question, since you're using Excel<=2003 (from comment), your "tail" column would be IV, which is #256,

so your largest available column number is 256, and calling Cells(4000,302) would be out-of-range.

For your second question, since you're using Excel<=2003, one row can only contain up to 256 columns,

so selecting A2:IV4000 (row #2 to #4000, col #1 to #256) is effectively equals to selecting 2:4000 (full row selection, row #2 to #4000), and Excel "shorten" that address for you.

answered Oct 12, 2012 at 8:24

Passerby

10.1k2 gold badges37 silver badges53 bronze badges

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

Collectives™ on Stack Overflow

Application-defined or object-define error when using Range

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related