gravanoc
Active Member
- Joined
- Oct 20, 2015
- Messages
- 353
- Office Version
- 365
- Platform
- Windows
- Mobile
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:
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.
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.
A basic dynamic named range AFAIK uses something like:
Excel Formula:
=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.
Excel Formula:
=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.