Help with understanding this VBA syntax: .End(3)(1)

Moderator's note: I modified your title to be more specific. Normally I would just issue a warning but in this case I sympathized. --6StringJazzer

Hi all,

The original questions is to create 4 quarter sheets from the annual sales sheet, and a smart guy helped me with this code. I tried this code, and it works well, but I have a hard time of understanding some portion of this code. what is the .End(3)(1) and .End(3)(2) Refer to? Any people have any idea? thank you very much !!

Please Login or Register  to view this content.

Any help is appreciated!!

Normally, you would see:
Range("A1").end(xlDown or xlToLeft or xlToRight or xlUp )
So the value in the first set of parentheses ranges from 1 to 4, corresponding to the named constants. The second set seems (?) to be an offset.

I think the (1) is redundant and the (2) will give you the cell 1 row down.

For example, Range("A1")(1,1) would be cell A1 and Range("A1")(3,2) would be cell B3

Regards, TMS

I believe protonLeah has the correct explanation. The first number in parentheses is an argument to the End attribute, and indicates which direction to go to reach the end. However, the built-in constants listed by protonLeah correspond to the following integers:

xlDown=-4121
xlUp=-4162
xlToLeft=-4159
xlToRight=-4161

I am not sure why a 3 works here but my test shows that it does the same thing as xlUp. (In my opinion using 3 here is a poor coding practice.)

The second number in parentheses is a Range index. Any time you have an expression that returns a Range, you can add (row[, column]) indexing after it to refer to a specific cell within that Range. The indexing is relative to the upper left corner of the range (it is an index, not an offset), and may refer to a cell outside the range. The index (1, 1) refers to the upper left corner cell. If column is omitted it defaults to 1, so (1) also refers to the upper left corner.

Please Login or Register  to view this content.

Let's work our way inside out to break this down.

Rows.count is the number of rows in the worksheet. For 2007+ this will be 1,048,576.
Range("A" & Rows.count) will be A1048576. Note that this is a Range.
Range("A" & Rows.count).End(3) starts in A1048576 and moves upwards until it finds a used cell. Note that this expression returns a Range.
Range("A" & Rows.count).End(3)(2) starts with the used cell just found, treats it as (1, 1), and then returns the cell located at (2, 1), or one row below it.

For example, if there are 15 rows of data, the cell returned by the above will be A16.

Administrative Note:

Welcome to the forum madmarq69.

We are happy to help, however while you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original. Please start a new thread - See Forum rule #4

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread