CurrentRegion is most handy in many cases, however I have (sadly) just discovered, it apparently has limitations.
I have boiled this down to its simplest form for posting purposes. This is not my real code, but it demonstrates the challenge.
If the function (below) is called from another function or subroutine, or, if it is simply run "stand alone" it does what I expect, it targets cell C3, expands the range, and the resultant address is displayed as B3:D6 (no problem).
However, if the function is used by inserting the formula "=MyFun()" into a cell in the spreadsheet, the CurrentRegion seems to be completely ignored and the function displays, and returns, simply "C3" (ignoring the ".CurrentRegion" portion of the s1 assignment).
Can someone share why this happens? What am I missing?
Thank you!!
Function MyFun() As String
Dim s1 As String
s1 = Worksheets("SHEET1").Range("C3").CurrentRegion.Address
MsgBox s1
MyFun = s1
End Function
-- removed inline image ---
I have boiled this down to its simplest form for posting purposes. This is not my real code, but it demonstrates the challenge.
If the function (below) is called from another function or subroutine, or, if it is simply run "stand alone" it does what I expect, it targets cell C3, expands the range, and the resultant address is displayed as B3:D6 (no problem).
However, if the function is used by inserting the formula "=MyFun()" into a cell in the spreadsheet, the CurrentRegion seems to be completely ignored and the function displays, and returns, simply "C3" (ignoring the ".CurrentRegion" portion of the s1 assignment).
Can someone share why this happens? What am I missing?
Thank you!!
Function MyFun() As String
Dim s1 As String
s1 = Worksheets("SHEET1").Range("C3").CurrentRegion.Address
MsgBox s1
MyFun = s1
End Function
-- removed inline image ---
