Dynamic named range that can accurately capture both row & column extremes

[gravanoc]

My goal is to capture the filter criteria using VBA when a user has multiple conditions active across several fields (columns). I've come very close to this, but ultimately there are still tilt scenarios that I can't leave unaddressed.
A basic dynamic named range AFAIK uses something like:

=OFFSET($C$2, 0, 0, COUNTA($C$2:$C$), 1)

My intent was to also capture the columns since the first subroutine will need to output data in an 2D array. I replaced the above formula's last argument with a span of columns, then I ran the code that reads the array into a variable, but it always had something wrong with it. Either too short, too long, or not capturing values after blanks, etc. I turned to experimenting on the formula on the spreadsheet, and finally came up with something.
This was my (super-convoluted) formula that produced the correct results on the spreadsheet, but wouldn't take as a DNR.

=OFFSET(Internals!$C$2,0,0,COUNTA(Internals!$C$2:$C$23)+COUNTBLANK(INDIRECT(CONCAT("$C$2:",ADDRESS(MATCH(LOOKUP(2,1/(Internals!$C$2:$C$23<>""),Internals!$C$2:$C$23),Internals!C2:C23,0)+1,3)))),MAX(COUNTA(Internals!C2:XFD2),COUNTA(Internals!C3:XFD3),COUNTA(Internals!C4:XFD4),COUNTA(Internals!C5:XFD5),COUNTA(Internals!C6:XFD6),COUNTA(Internals!C7:XFD7),COUNTA(Internals!C8:XFD8),COUNTA(Internals!C9:XFD9),COUNTA(Internals!C10:XFD10),COUNTA(Internals!C11:XFD11),COUNTA(Internals!C12:XFD12),COUNTA(Internals!C13:XFD13),COUNTA(Internals!C14:XFD14),COUNTA(Internals!C15:XFD15),COUNTA(Internals!C16:XFD16),COUNTA(Internals!C17:XFD17),COUNTA(Internals!C18:XFD18),COUNTA(Internals!C19:XFD19),COUNTA(Internals!C20:XFD20),COUNTA(Internals!C21:XFD21),COUNTA(Internals!C22:XFD22),COUNTA(Internals!C23:XFD23)))

Basically, I believe that the hangup is related to the INDIRECT formula being used, so if I could recall a non-volatile way to concatenate two cells into a range, that would probably solve the issue. It will still need to find the last cell in the row (or column for that matter), so that the range's end point is known.

Thanks for clearing any of this up for me.

 

[Alex Blakenburg]

I am little confused in that you mention using VBA but seem to be only using excel formulas.

Your profile says you have 365 so the below should return the last value in the column.

XLOOKUP(TRUE,(NOT(ISBLANK(A:A))),A:A,"Unknown",0,-1)

And this the last row.

ROW(XLOOKUP(TRUE,(NOT(ISBLANK(A:A))),A:A,"Unknown",0,-1))

 

[gravanoc]

Thanks, Alex. I will try these out.

 

[Alex Blakenburg]

Thanks for the update, let me know how you go.