excel - 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?

Answer 1

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 ?