Why does using the Range.Resize property not output the expected Address property value?

Question

I was answering another question and came across unexpected results when using Range.Resize(1, 1).

I was looking to resize the Target property of the Worksheet_SelectionChange if a user selected more than 1 cell. Specifically I was intending to change the Target range to the top left cell of the selected range.

I noticed when testing this the resized Target range would not always result in the expected cell, often resulting in a column or row not within the original selection.

Testing code is as follows and;

outputs to the immediate window with |
Outputs to the immediate window the result of Target.Resize(1, 1).Address(False, False)
Assigns the result of Target.Resize(1, 1).Address(False, False) to Target
Outputs to the immediate window the new results of |

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   Debug.Print Target.Count & " | " & Target.Address(False, False)
   Debug.Print "Resize Address: " & Target.Resize(1, 1).Address(False, False)
   Set Target = Target.Range(Target.Resize(1, 1).Address(False, False))
   Debug.Print Target.Count & " | " & Target.Address(False, False)
End Sub

Side note: The cause of the issue is Set Target = Target.Range(Target.Resize(1, 1).Address(False, False)) Specifically Set Target = Target.Range... should be Set Target = Me.Range... or just Set Target = Range.

Consider the following selections are made on the worksheet;

Range("A1:C10) and Range(C1:E10")

I expect the Debug.Print method of my test code to output:

30 | A1:C10
Resize Address: A1
1 | A1
30 | C1:E10
Resize Address: C1
1 | C1

The result I get is:

30 | A1:C10
Resize Address: A1
1 | A1
30 | C1:E10
Resize Address: C1
1 | E1    '<~~~~~

Although the address returned from the resize property is C1 the range is set as E1 I've noticed it appears to set the new column to the right of the new target column the count of columns there are to the left of the new target column (i.e if I select F1:F10 on the worksheet, it will resize to K1. Column F is 5 columns to the right of Column A and Column K is 5 columns to the right of Column F.

Why is this happening?

user_11643528 · Answer

This line:

Target.Range(Target.Resize(1, 1).Address(False, False))

is taking a range within an existing range. When that range starts at A1 .resize(1, 1) has an index of 1 so no offsetting of the original range.

When you start at C1 .resize(1, 1) now has an index of 3, the relative index of that range is C - 1, D - 2, E - 3. When you take the range of the range it offsets by 3 giving you E1.

Private Sub test()
    Dim r As Range
    Set r = Range("A1:E10")
    Debug.Print r.Address, r.Resize(1, 1).Address, "Normal Resize"
    Debug.Print r.Address, r.Range(r.Resize(1, 1).Address).Address, "Range offset resize"
    Set r = Range("C1:E10")
    Debug.Print r.Address, r.Resize(1, 1).Address, "Normal Resize"
    Debug.Print r.Address, r.Range(r.Resize(1, 1).Address).Address, "Range offset resize"
End Sub

Why does using the Range.Resize property not output the expected Address property value?

1 Answers