0

I am trying to get a range corresponding to the region returned by CTRL-A when on cell B3, which is B3:J9 for the setup below :

enter image description here

I have the following code to do so, which uses CurrentRegion in a function :

Option Explicit
Function TestY(msg As Range) As Range

    Dim rg As Range, tl As Range

    Set tl = msg.Cells(1)
    Debug.Print tl.Row, tl.Column

    Set rg = tl.CurrentRegion
    Debug.Print rg.Count, rg.Cells(rg.Count).Row, rg.Cells(rg.Count).Column
        
End Function

However the debug log below shows that this does not work, tl.CurrentRegion is just the same as msg.Cells(1) :

 3             2 
 1             3             2

Why is CurrentRegion not working here and how can I fix it to return the same as CTRL-A when on B3 ?

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
user3203476
  • 499
  • 3
  • 12
  • 1
    Probably better to pass the range in question as a parameter. `CurrentRegion` doesn't work in UDFs. – BigBen Mar 15 '21 at 14:46
  • You could use the [refCurrentRegionBottomRight function](https://stackoverflow.com/questions/66308643/calculating-sub-totals-within-a-dynamic-array-range/66312460#66312460): `Set rg = refCurrentRegionBottomRight(Range("B3"))`. – VBasic2008 Mar 15 '21 at 15:48

0 Answers0