## Multiply cells efficiently

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

### User defined functions udf

The TEXTJOIN function introduced in excel 2016 allows you to concatenate values easily. It also accepts arrays and nested functions. However if your excel version is missing the TEXTJOIN function you can use a User Defined Function, I have all […]

You have quite a few options to choose from if you are looking for a way to create a unique distinct list in your workbook, all demonstrated in this post or on this website. I would choose the advanced filter […]

Today I have two functions I would like to demonstrate, they calculate all possible combinations from a cell range. What is a combination? To explain combinations I must explain the difference between combinations and permutations. Think of permutations as if the order is […]

### Vba

You have quite a few options to choose from if you are looking for a way to create a unique distinct list in your workbook, all demonstrated in this post or on this website. I would choose the advanced filter […]

In a previous post: How to create a dynamic pivot table and refresh automatically in excel I demonstrated how to refresh a pivot table when a sheet is activated. This post describes how to refresh a pivot table when data is […]

Today I´ll show you how to search all excel workbooks (xls, xlsx, xlsm) in a folder for a text string. The macro creates a new sheet containing the search result. Each result contains a link to a cell where the […]

### 2 Responses to “Multiply cells efficiently”

### Leave a Reply

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

<code>your formula</code>

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

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.