## Resize a range of values

*Article updated on January 19, 2018*

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.

### Arguments

*range*,

*rows*,

*columns*)

*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:

*C2:C17*,

*0*,

*4*)

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

*C2:C17*,

*2*,

*0*)

### 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.

- Press and hold CTRL + SHIFT simultaneously
- Press Enter
- Release all keys

If you did it right the formula now has a curly bracket before and after. Like this {=ResizeRange(*C2:C17*, *2*, *0*)}. 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

Split search value using delimiter and search for each substring

Anil asks: I have A1(anil singh raj) It can be anything Like A1(singh raj anil) I want return value in […]### One Response to “Resize a range of values”

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

Remember to convert less than and larger than signs to html character entities before you post your comment.

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

I got it, Thank you