## Resize a range of values

*Article last 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

Create links to all sheets in a workbook

The macro demonstrated below creates hyperlinks to all worksheets in the current worksheet. You will then be able to quickly […]

Finding the shortest path – A * pathfinding

Two months ago I posted some interesting stuff I found: Shortest path. Let me explain, someone created a workbook that calculated […]

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 […]

Combine cell ranges eliminating blanks

This is an answer to Shawna´s question. The following user defined function merges up to 255 cell ranges and removes […]

Did you know that you can select all comments in the current sheet? Press F5, click "Special..." button, select "Comments" […]

Debra has a great post and video about normalizing data for excel pivot table. This post describes a macro that normalizes […]

Question: I have a text name coloumn, there will be only 1 value to be listed in 1 coloumn in […]

The picture above shows data presented in only one column (column B), this happens sometimes when you get an undesired […]

Here is another macro to normalize data. Scenario: The user has entered multiple values in the same cell. The macro […]

### One Response to “Resize a range of values”

### Leave a Reply

### How to comment

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

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

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

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

Put your VBA code here.

[/vb]

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

Upload picture to postimage.org or imgur

Use the img tag, like this: <img src="Insert pic link here">

**Contact Oscar**

You can contact me through this contact form

I got it, Thank you