Multiply each value in a range by a constant, but skip blank cells

Question

I need a simple a fast solution for multiplying all values in a range by a numeric value in VBA code. I know about this solution: Multiply Entire Range By Value?

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

But it has a big drawback - if the original cell was blank, it results in zero. How to force it skip blank values?

I want to avoid looping through the values because it is very slow.

Answer 1

You can use your existing approach with Evaluate but get a little smarter with it - it can take conditions etc, so just include a test for ISBLANK. This example is tested on a combination of blank and non-blank cells in the range A1:C3 - just update for your range and give it a try:

Option Explicit

Sub Test()

    Dim rng As Range

    Set rng = Sheet1.Range("A1:C3")

    'give the name a range so we can refer to it in evaluate
    rng.Name = "foo"

    'using Evaluate
    rng = Evaluate("IF(ISBLANK(foo),"""",foo*2)")

    'using [] notation
    'preferred IMO as dont need to escape "
    rng = [IF(ISBLANK(foo),"",foo*2)]

End Sub