The user defined function demonstrated below, resizes a range you specify to columns or rows you also specify. The first argument is the range, second argument is how many columns you want and third argument is how many rows you want in your new range.

Resize a range3

Arguments

=ResizeRange(rangerowscolumns)

range - cell range you want to resize

rows - the number of rows you want, leave it to 0 (zero) if you want the udf to calculate the number of rows needed

columns - the number of columns you want, leave it to 0 (zero) if you want the udf to calculate the number of columns needed.

The picture above shows values in column c, C2:C17 being resized into a range of 4 columns. The user defined function calculates the number of rows that is needed, to do that use 0 (zero) as the  row argument:

=ResizeRange(C2:C1704)

If you want 2 rows and and as many columns as needed the udf becomes:

=ResizeRange(C2:C1720)

How do I use this?

Copy the following code to a module. How to insert a module to a workbook.

Function ResizeRange(rng As Range, r As Single, c As Single)
Dim rngV As Variant
Dim tbl() As Variant
Dim Value As Variant
rngV = rng.Value
If r = 0 Then
r = Application.WorksheetFunction.RoundUp(rng.Cells.CountLarge / c, 0)
ElseIf c = 0 Then
c = Application.WorksheetFunction.RoundUp(rng.Cells.CountLarge / r, 0)
End If
ReDim tbl(1 To r, 1 To c)
r = 1
c = 0
rngV = Application.Transpose(rngV)
For Each Value In rngV
If c = UBound(tbl, 2) Then
r = r + 1
c = 0
End If
c = c + 1
tbl(r, c) = Value
Next Value
ResizeRange = tbl
End Function

Go back to Excel from VB Editor.

Select a cell range, type the udf and it's arguments. See animated picture at the beginning of this post.

Create an array formula, here are the details if you don't know how to.

  1. Press and hold CTRL + SHIFT simultaneously
  2. Press Enter
  3. Release all keys

If you did it right the formula now has a curly bracket before and after. Like this {=ResizeRange(C2:C1720)}. Don't enter these yourself.

Explaining the user defined function

Function name and arguments

A user defined function always starts with "Function" and then a name. This udf has a three arguments. Variable rng is a range object, r and c are declared as Single. Read more about Defining data types.

Function ResizeRange(rng As Range, r As Single, c As Single)

Declaring variables

Value, tbl and rngV are all variants. tbl has two parenthesis meaning it is an array. Read more about Defining data types.

Dim rngV As Variant
Dim tbl() As Variant
Dim Value As Variant

Convert values from rng (range object) to rngV (variant array)

This speeds up the function considerably if you are working with large cell ranges. Excel copies all the values from the sheet and puts them in memory (array).

rngV = rng.Value

If ... then... ElseIf ... End If

Checks if r equals to 0 (zero) and if true then calculates needed rows. Checks also if c equals to 0 (zero) and then if true calculates needed columns.

If r = 0 Then
r = Application.WorksheetFunction.RoundUp(rng.Cells.CountLarge / c, 0)
ElseIf c = 0 Then
c = Application.WorksheetFunction.RoundUp(rng.Cells.CountLarge / r, 0)
End If

Build array

Now we know how many columns and rows we need to build the array. ReDim changes the array size.

ReDim tbl(1 To r, 1 To c)

Use variables r and c to save values in array

I am reusing these variables to help me know where to put each rngV value in tbl array. r is equal to 1 and c is equal to 0 (zero).

r = 1
c = 0

Transpose array

An array with 2 rows and 4 columns becomes an array with 4 rows and 2 columns, read more about transposing an array or range.

rngV = Application.Transpose(rngV)

For ... Next statement

Repeats a group of statements a specified number of times.

For Each Value In rngV

Next Value

If ... then... End If

Checks if c is equal to the number of columns in tbl array then, if true, adds 1 to r and c is equal to 0 (zero)

If c = UBound(tbl, 2) Then
r = r + 1
c = 0
End If

Add 1 to variable  c

c is equal to c + 1

c = c + 1

Save value to tbl array

r is the row number and c is the column number in tbl array.

tbl(r, c) = Value

Return tbl array values to function

The ResizeRange returns an array of values stored in tbl.

ResizeRange = tbl

End a udf

A function procedure ends with this statement.

End Function

Download excel *.xlsm file

Change number of rows and columns in a range.xlsm