I can probably work around this, but I don't understand why it is happening.
The attached spreadsheet has everything set up. Here is the code:
Option Explicit
Sub weird()
Dim goodr, badr As Range
Dim x, y, lrow As Long
lrow = Cells(Rows.Count, 2).End(xlUp).Row
x = Sheets("Sheet1").Range("1:1").Find("d").Column
y = Sheets("Sheet1").Range("1:1").FindPrevious.Column
Set badr = Columns(x).Resize(lrow, y - x + 1)
Range("A10") = "badr address is: " & badr.Address
Set goodr = Range("D1:F7")
Range("A11") = "goodr address is: " & goodr.Address
Range("A12") = "badr(2) address is: " & badr(2).Address
Range("A13") = "goodr(2) address is: " & goodr(2).Address
End Sub
Here is the output:
badr address is: $D$1:$F$7
goodr address is: $D$1:$F$7
badr(2) address is: $E$1:$E$7
goodr(2) address is: $E$1
I don't understand how these two ranges can have the same address, yet behave differently when using the (2) offset. I thought it always went by cell (and that is what my other code was expecting).
Thanks
Bookmarks