you could see avideo demo of this approach here.
The next two arguments are offsets for rows and columns and are supplied as zero.
The last two arguments represent height and width.
Height and width are generated on the fly by using COUNTA, which makes the resulting reference dynamic.
For height, we use the COUNTA function to count non-empty values in the range B5:B100.
This assumes no blank values in the data, and no values beyond B100.
For width, we use the COUNTA function to count non-empty values in the range B5:Z5.
This assumes no header cells, and no headers beyond Z5.
Note: The ranges used for height and width should be adjusted to match the worksheet layout.
The advantage of this approach is the simplicity of the ranges inside COUNTA.
OFFSET is handy in formulas that require a dynamic range.
COUNTA does not count empty cells.
TRIMRANGE Function
The Excel TRIMRANGE function removes empty rows and columns from a range of data.
The result is a “trimmed” range that only includes data from the used portion of the range.
