Multiply Entire Range By Value?

Question

So The best way I could think of to accomplish this over a large range (about 450k rows) was to use the following Sue-do code:

Range("A1").Copy ' A1 Contains Value I want to multiply column by
Range("MyTable[FooColumn]").PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply

Now this works, but the fact that I have to copy and paste that value seems redundant as the value is never going to change.

For Each c In Range("MyTable[MyColumnHeader]")
    If IsNumeric(c) And Not c = "" Then
        c.Value = c.Value * 453.592 ' The value that is in A1 from previos sample
    End If
Next

That works, but is slower. As it has to loop every cell.

I also tried:

With Range("MyTable[MyColumnHeader]")
    .Value = .Value * 453.592
End With

But received runtime error Type Mismatch Error if there was more then one value in the column.

I thought about inserting a column and using the formulaR1C1 of "=R-1C * 453.592" Then .Value = .Value, Then shift the column and overwrite but seemed clunky and I would think also slower then the paste multiply.

So, does anyone have any better ways of accomplishing this task?

Paste special multiply followed by paste special values seems like the only improvement on your initial method — Floris, Sep 24 '13 at 19:41
If the cell contains a formula rather than a number, a paste special-multiply causes a `*number` to be added to the cell - for example, `SIN(1)` becomes `(SIN(1))*453.592`. If you only need to do this once, you will make the spreadsheet more compact (and perhaps a little faster to load / recalculate) by replacing each formula with the value. But your paste special by itself is, in my mind, a perfectly good solution. Not sure why you think it is "redundant" - you tell Excel "use this value, apply it here, with multiplication". There is really no shorter way. — Floris, Sep 24 '13 at 22:36
@Floris The column doesn't contain any formulas, already is value only. If the column had formulas already i would just change the formula to include the number in the vba instead that would be much faster then the copy paste. I find it reduntent to have vba need to make a call to the worksheet everytime i run the code as opposed to eing able to set the value of the entire column at once by a stored variable inside the vba workflow. — user2140261, Sep 24 '13 at 23:11
I don't understand your "everytime I run the code" comment. Once you have multiplied the numbers why would you have to repeat it? They will already be at their new value. If you do it again, they will get bigger again, surely. I understand less and less what you are trying to achieve... — Floris, Sep 24 '13 at 23:18
@Floris This data comes from external sources,imported manipulated then exported. It is not complicated, I have a column with rows of values (no formulas) I need a very fast way to multiply a value across an ever changing range of ~500k rows.The background of WHY doesn't affect my question, I showed some basic ways of accomplishing this, the one I found the best, now I am asking if it can be improved, not because it doesn't work but just it seems like there should be a better way. Basically a way that avoids all loops and interaction with worksheet, besides a single read and a single write. — user2140261, Sep 24 '13 at 23:44
You want to see the fastest method of achieve what you want? If yes, then see `Way 2` in [THIS](http://stackoverflow.com/questions/27070173/excel-vba-beginner-error) link. — Siddharth Rout, Nov 21 '14 at 21:46
@SiddharthRout You just used my example from my question,(the first set of code in the question) and your way 1 is the accepted answer to my question... Why even bother commenting? You have added nothing to this question. — user2140261, Feb 26 '15 at 00:58

score 16 · Accepted Answer · edited Mar 27 '14 at 21:21

16

Sub Test()

    Dim rngData As Range

    Set rngData = ThisWorkbook.Worksheets("Sheet1").Range("A1:B10")
    rngData = Evaluate(rngData.Address & "*2")
End Sub

Kind of outdated but is that what you were looking for ?

edited Mar 27 '14 at 21:21

user2140261

7,855
7
32
45

answered Mar 25 '14 at 08:19

user2359459

212
2
11

This is pretty much what I am looking for I like this, it is still slower then the method in my question but only on average by about 5MS across 10,000 cells. its a great answer and a neat way to get the job done! Thanks for your input! – user2140261 Mar 27 '14 at 21:20
3

For anyone trying to make use of this, dont forget to assign rngData back to the range, otherwise there is no change on the worksheet itself (duh!). – NeverStopLearning Mar 01 '15 at 10:47
3

@NeverStopLearning Why don´t you edit the answer so people new to VBA (as am I) could use the code correctly? :c – Cris May 22 '18 at 16:33
While I am sure that @Cris has probably moved on by now, [my answer](https://stackoverflow.com/a/73408581/5781745) was an attempt to address Cris' concerns to assist those who may have felt the same. – K.Dᴀᴠɪs Aug 18 '22 at 19:41

score 1 · Answer 2 · answered Sep 24 '13 at 23:58

1

Do not update cell by cell. It is very slow and there is a better way with VBA. Here is the outline:

Set a Range to all rows/columns needed to process
Copy values into array in VBA
Process the array
Write the array back into the worksheet in one operation

Here is an example:

Public Sub FactorRange(ByRef r_first as Range, ByVal N_rows as Long, _
ByVal N_cols as Long, ByVal factor as Double)
    Dim r as Range
    'Set range from first cell and size
    Set r = f_first.Resize(N_rows,N_cols)
    Dim vals() as Variant
    ' Copy cell values into array
    vals = r.Value
    Dim i as Long, j as Long
    ' Do the math
    For i=1 to N_rows
      For j=1 to N_cols
        vals(i,j) = factor * vals(i,j)
      Next j
    Next i
    ' Write values back
    r.Value = vals
End Sub

answered Sep 24 '13 at 23:58

John Alexiou

28,472
11
77
133

I have already tested this method, this takes about 750% longer then the way I am doing it, copy and paste. This method takes on average 2.38s while mine only takes 0.32s, when calculating 500k rows. This method still loops every single item even if its in an array instead of the worksheet, my solution avoids all loops. Is there away to manipulate the entire array without a loop then it might be faster if not then there is no answer to this question. – user2140261 Sep 25 '13 at 12:24
Hey maybe you have to use a `FORTRAN` dll from VBA then if speed it what you want. – John Alexiou Sep 28 '13 at 17:09

score 1 · Answer 3 · answered Aug 18 '22 at 19:38

In response to Cris' request to one of the answers here, this is a modification to the answer that would make the answerer's method simpler to use for those who didn't understand the workings behind the answer.

The Code:

Sub EvaluateRange(Rng As Range, EvalStr As String)

    Rng.Value = Evaluate(Rng.Address & EvalStr)

End Sub

Simple subroutine. The old values in the supplied range Rng.Value = will be updated to the evaluated string. The sub requires two arguments Rng and EvalStr:

Rng: The range that contains the data to be evaluated. This is also the range that will be replaced upon the evaluation's completion.

EvalStr: The operation that you would like applied to the entire range. For example, if you wanted to multiply the entire range by 100, then EvalStr = "*100".

Example of Usage:

Assume you wanted to multiply these 5 cells (A1:E1) by 100 within VBA:

You would call the above code as such:

EvaluateRange Worksheets("Sheet2").Range("A1:E1"), "*100"

Now those 5 cells would appear as:

While I'm sure the person who left the comment that encouraged this answer's creation is long-gone by now, hopefully others in this community will find this answer to be useful (their comment had 3 upvotes, so I assume that there are others who feel the same as they did).

Further note: Given a fully qualified setting of the `rng` argument a **worksheet related evaluation** via `rng.parent.Evaluate(...)` guarantees a fully qualified range reference, whereas an unprefixed `Evaluate(...)` would need more detailed range indications like e.g. Sheet2!A1:E1 or a further textual insertion of `rng.Address(External:=True)` to the formula string to be evaluated. @K-Davis — T.M., Dec 06 '22 at 20:33

score 0 · Answer 4 · answered Jun 04 '21 at 16:42

I needed the same thing. Here's how I did it. This approach temporarily uses a cell to store the "1" to multiply by with paste special.

Hold = Range("A1").Formula  ' Save any existing value in A1 to Hold
Range("A1").Value = 1       ' Temporarily replace value with "1"
Range("A1").Copy            ' Copy "1" to clipboard
Columns("A:A").PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply  ' Paste multiply
Range("A1").Formula = Hold  ' Restore original A1 formula (or value, if it wasn't a formula)
Application.CutCopyMode = False  ' Clear the clipboard

score -1 · Answer 5 · answered Apr 17 '15 at 09:34

return_sheet = ActiveSheet.Name
ActiveWorkbook.Sheets.Add
ActiveSheet.Name = "CopyPaste"
Selection.Value = 1
Selection.Copy
Sheets(return_sheet).Select 'if necessary select range you whant to multiply
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
    Application.DisplayAlerts = False
    Sheets("CopyPaste").Delete
    Application.DisplayAlerts = True
Sheets(return_sheet).Select

Multiply Entire Range By Value?

5 Answers5

The Code:

Example of Usage:

Linked

Related