Someone on an email distribution list I’m on said it would be really useful to have a function that mimics the behavior of VBA’s Range.CurrentRegion.

I thought this would be an interesting challenge to implement with LAMBDA, so here’s one way you might do it:

CURRENTREGION = LAMBDA(ref,
    LET(
        new_start_row,MIN(ROW(ref)),
        new_start_col,MIN(COLUMN(ref)),
        height_change,MIN(2,2^20-(new_start_row+ROWS(ref))),
        width_change,MIN(2,2^14-(new_start_col+COLUMNS(ref))),
        newref,TRIMRANGE(
            OFFSET(
                ref,
                IF(new_start_row=1,0,-1),
                IF(new_start_col=1,0,-1),
                ROWS(ref)+height_change,
                COLUMNS(ref)+width_change
            )
        ),
        same,IFERROR(AND(ref=newref),FALSE),
        IF(same,ref,CURRENTREGION(newref))
    )
);

Here’s an example of the function being used. You can see that the formula in cell C24 is passing F15 to CURRENTREGION, which is then returning the current region from that cell – the equivalent of pressing Ctrl+A. Importantly, it captures the 11 in B13 even though it’s not directly connected to any other data.

The basic idea here is that for any reference ‘ref’ passed to the function, create a new reference ‘newref’ by using OFFSET to increase the size of ‘ref’ by one row or column in every direction (taking into account the edges of the spreadsheet), then call TRIMRANGE on the result. This has the effect of removing any blank rows or columns added by OFFSET.

        newref,TRIMRANGE(
            OFFSET(
                ref,
                IF(new_start_row=1,0,-1),
                IF(new_start_col=1,0,-1),
                ROWS(ref)+height_change,
                COLUMNS(ref)+width_change
            )
        ),

After doing so, the original reference might have increased in size (because adjacent rows or columns have data in them). If that’s the case, then the ‘same’ name returns FALSE.

        same,IFERROR(AND(ref=newref),FALSE),
        IF(same,ref,CURRENTREGION(newref))
    )
);

In the final line, we check if ‘same’ is TRUE. If it is, then the new rows and columns added by OFFSET have all been removed by TRIMRANGE, which is the same as saying all surrounding rows and columns are blank. Which is to say ‘ref=newref’ and we just return ‘ref’ as the current region.

If ‘ref’ and ‘newref’ aren’t the same, then there are some adjacent rows or columns with data in them, so we pass ‘newref’ back into CURRENTREGION.

This process repeats with the newly expanded reference until the point where ‘ref’ is equal to ‘newref’. ‘newref’ is the current region, because by definition it is surrounded by blank rows and columns or the edges of the spreadsheet.

I’m not ashamed to admit I stumbled through several other more complicated versions of this function before landing on this one, which for now is the simplest I could think of. It’s amazing what opportunities TRIMRANGE creates!

If you’d like to read more about LAMBDA, check out these other posts.