Why does `PasteSpecial` method force the activecell to change?

Question

I'm surprised there aren't really any great ways to multiply a Range by a constant value. Sure you can use a loop, but that seems pretty long-winded for something that seems so simple. You can also use the Evaluate statement, but the information page is so vague on what this actually does, so I would rather not. That leaves us with the PasteSpecial method, which for some reason forces the active cell to change. This is annoying, because if you want to keep the activecell default on the page you're pasting it onto, you now have to add 4 lines of code to achieve it.

Before:

Sub sq()

Range("A1").Copy

Sheets(2).Range("F1:F20").PasteSpecial xlPasteValues, xlPasteSpecialOperationMultiply

Application.CutCopyMode = False

End Sub

After:

Sub sq()


Sheets(1).Range("A1").Copy

Sheets(2).Activate

adr = ActiveCell.Address

Sheets(2).Range("F1:F20").PasteSpecial xlPasteValues, xlPasteSpecialOperationMultiply

Application.CutCopyMode = False

Sheets(2).Range(adr).Select

Sheets(1).Activate


End Sub

Is this the generally accepted method of simply multiplying a range by a value or am I unaware of something else?

score 1 · Answer 1 · edited May 23 '17 at 12:14

If you are looking for a 'cool' method of applying a Paste Special, Multiply to a range of cells without using .Select, consider adding a UDF to your project.

Sub sq()
    Dim rMultiplier As Double

    With Worksheets(1)
        rMultiplier = .Range("A1").Value2
    End With

    With Worksheets(2).Range("F1:F20")
        .Cells = udfPasteSpecialMultiply(.Cells, rMultiplier)
    End With

    With Worksheets(2).Range("H2:K2")
        .Cells = udfPasteSpecialMultiply(.Cells, rMultiplier)
    End With

End Sub

Function udfPasteSpecialMultiply(rng As Range, dMult As Double)
    Dim v As Long, w As Long, vVALs As Variant

    vVALs = rng.Value2
    For v = LBound(vVALs, 1) To UBound(vVALs, 1)
        For w = LBound(vVALs, 2) To UBound(vVALs, 2)
            vVALs(v, w) = vVALs(v, w) * dMult
        Next w
    Next v

    udfPasteSpecialMultiply = vVALs
End Function

The User Defined Function returns an array of modified values that are stuffed back into the cells after performing the equivalent of a [Range.PasteSpecial method] with the xlPasteSpecialOperationMultiply operation. No change to the selection or active cell on either worksheet is made.

In all fairness, you really shouldn't be relying on .Select, .Activate, ActiveCell or Selection to reference cells. As your coding skills develop these cell reference problems are going to get worse; not better. See How to avoid using Select in Excel VBA macros for more methods on getting away from relying on select and activate to accomplish your goals.

Thanks. I still think it's a little cumbersome to have a for...loop just for multiplication but of course this is the cleanest option. — apdm, Sep 25 '15 at 13:59
fwiw, the loop operation is performed on an in-memory variant array image of the cells and not the cells themselves. Only after the operation has been completed are the array values dumped back into the cells. — , Sep 25 '15 at 14:01

Why does `PasteSpecial` method force the activecell to change?

1 Answers1