## Multiply cells efficiently

Joe asks:

Hello Oscar,

I have an Excel dataset consisting of 500 rows by 7 columns. I need to generate additional datapoints from this dataset. I want to multiply (or other function) each row by all 500 rows, creating 250,000 new rows of data. Each cell needs to function as a constant that is multiplied by all the other cells in the same column (which are not acting as constants).

How do I do this efficiently? Thanks in advance!

### Answer

I don't think you can multiply cells like that with an array formula, as far as I know.

Here is a user defined function I made, it multiplies each row by all other rows in the specified range.

The picture above shows you the udf using values in cell range B2:B3 and returns the calculated values to cell range B5:D8.

### Function and argument

This is a user defined function or custom function, you need to copy code to code module before you can use it.

*range*)

There is only one argument in this udf.

*range* - Cell range

### How do I use this?

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

Function MultiplyCells(rng As Range) Dim rng1 As Variant Dim tbl() As Variant Dim rr As Single, r As Single, c As Single, tr As Single rng1 = rng.Value ReDim tbl(1 To rng.Cells.Rows.CountLarge ^ 2, 1 To rng.Cells.Columns.CountLarge) tr = 1 For rr = LBound(rng1, 1) To UBound(rng1, 1) For r = LBound(rng1, 1) To UBound(rng1, 1) For c = LBound(rng1, 2) To UBound(rng1, 2) tbl(tr, c) = rng1(rr, c) * rng1(r, c) Next c tr = tr + 1 Next r Next rr MultiplyCells = tbl End Function

Enter this udf as an array formula, if the range has 2 rows and 3 columns enter the udf in a cell range with 4 (2*2) rows and 3 columns.

Here are the steps to enter this udf as an array formula

- Select cell range
- Type udf name and argument (=MultiplyCells(B2:D3)
- 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 {=MultiplyCells(B2:D3)}. Don't enter these yourself. If you are unsure, watch the formula bar carefully in the animated picture above.

Don't forget to save your workbook as a *.xlsm file.

### Learn code statements

The code explained here is located in the code module. You can find the code module in the VB Editor, press Alt+F11 to open the VB Editor. Click "Module" found in project explorer.

**Function name and arguments**

A user defined function always starts with "Function" and then a name. This udf has only one argument. Variable rng is a range object, read more about Defining data types.

`Function`

`MultiplyCells(rng `

`As`

`Range)`

**Declaring variables**

tbl() is an array and a variant variable, tbl has two parenthesis meaning it is an array. rng1 is also a variant. rr, r, c and tr are Single variables. Read more about Defining data types.

Dim rng1 As Variant Dim tbl() As Variant Dim rr As Single, r As Single, c As Single, tr As Single

**Save values from rng (range object) to rng1 (variant)**

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

rng1 = rng.Value

**Build array**

ReDim dimensions the tbl array variant, it has the number of rows of the range argument with the power of 2. The values in the array are numbered from 1 to n. There are as many columns as in the range argument.

`ReDim`

`tbl(1 `

`To`

`rng.Cells.Rows.CountLarge ^ 2, 1 `

`To`

`rng.Cells.Columns.CountLarge)`

**Use variables r and c to save values in array**

Variable tr keeps track of where to save the next column values in tbl.

tr = 1

**For ... Next statement**

Repeats a group of statements a specified number of times, here we want to multiply each row by all rows.

For rr = LBound(rng1, 1) To UBound(rng1, 1) For r = LBound(rng1, 1) To UBound(rng1, 1) For c = LBound(rng1, 2) To UBound(rng1, 2)

**Save value to tbl array**

The variables tr, c, rr and r help us keep track of which values to use and where to save.

tbl(tr, c) = rng1(rr, c) * rng1(r, c)

**Add 1 to variable tr**

tr = tr + 1

**Return tbl values to function**

MultiplyCells = tbl

**End udf**

A function procedure ends with this statement.

End Function

### Download excel *.xlsm file

Multiply cells efficiently.xlsm

### 2 Responses to “Multiply cells efficiently”

### Leave a Reply

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

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

your 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

If I understand the question correctly, this function would work:

First you would put your original 500 rows in a table somewhere ("Table1"), and then enter this function as an array (ctrl-shift-enter) starting in cell A1, with seven columns and 250,000 rows. It can be adapted with subtraction from row() and column() if you wish to put your data in a location other than cell A1.

-Alex

Alex,

Great comment!

It works fine, why didn't I think of this.

Your formula is small and genius.

Thank you for commenting.