## Resize a range of values

The user defined function demonstrated in the animated ggif 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 above creates hyperlinks to all worksheets in the current worksheet. You will then be able to quickly […]

How to create a list of comments from a worksheet programmatically

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

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

Combine cell ranges ignore blank cells

The image above demonstrates a user defined function that merges up to 255 cell ranges and removes blanks. I will also […]

To be able to use a Pivot Table the source data you have must be arranged in way that a […]

Rearrange values using formulas

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

Prepare data for Pivot Table – How to split concatenated values?

This article demonstrates a macro that allows you to rearrange and distribute concatenated values across multiple rows in order to […]

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

Paste image link to your comment.

**Contact Oscar**

You can contact me through this contact form

I got it, Thank you