If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.

RESIZEARRAY

Thread starter RicoS
Start date Mar 4, 2021

=RESIZEARRAY(array,r,c,defaultValue)

array: array to be resized

r: number of rows in resized array

c: number of columns in resized array

defaultValue: value entered in any rows or columns out-with the original array

RESIZEARRAY resizes an array to a given number of rows and columns, allowing for a default value where columns and rows are out-with the original array.

RicoS

Board Regular

Mar 4, 2021

RESIZEARRAY resizes an array to a given number of rows and columns, allowing for a default value where columns and rows are out-with the original array

Excel Formula:

=LAMBDA(array,r,c,defaultValue,
   LET(
      rA,ROWS(array),
      cA,COLUMNS(array),
      seqR,SEQUENCE(IF(r=0,rA,r)),
      seqC,SEQUENCE(,IF(c=0,cA,c)),
      return,IF(seqR>rA,defaultValue,IF(seqC>cA,defaultValue,INDEX(array,seqR,seqC))),
   return)
)

Lambda - Last Cell and Split.xlsx