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.

i'm using this on my macros. rngData.TextToColumns Destination:=rngData(1), DataType:=xlFixedWidth, _ FieldInfo:=Array(0, 1), TrailingMinusNumbers:=True — winghei, Aug 13 '16 at 08:51
Have you tried Excel function: PRODUCT? It ignores blank cells in the range — Anastasiya-Romanova 秀, Aug 13 '16 at 09:01
Anastasia - can you elaborate on how PRODUCT can be of any help here? If you use PRODUCT(A1, 5) where A1 is blank, you get 5. I am afraid it is a totally useless function which is extremely error prone. I would never use it in my worksheet. It actually does not skip blank cells, it only treats them as number 1. — HiFile.app - best file manager, Aug 13 '16 at 09:14
Of course I know Excel is tragically flawed in handling blank cells. Actually blank cell should be BLANK! It is not zero, it is not 1, it is blank. But Excel treats them as zeros most of the time. Or as 1s in PRODUCT. OMG, why? Sorry for this rant... I hate Excel. :( — HiFile.app - best file manager, Aug 13 '16 at 09:17
oh, i missed the part that you're trying to multiply a value. did you try using the copy and paste special? check on this link for example http://www.mrexcel.com/forum/excel-questions/927126-visual-basic-applications-paste-special-multiply-1-a.html/ . — winghei, Aug 13 '16 at 09:23
Either your title post: "multiply values in a range but skip blank cells" is misleading or it's exactly what you want. Assuming you want to multiply every value in the specific range, then yes PRODUCT formula is the way to go. I don't think Excel is terrible at handling blank cells in this case. I suspect it's the way this formula (PRODUCT) created. Either it ignores blank cells or simply treat them as cells with the values of 1. — Anastasiya-Romanova 秀, Aug 13 '16 at 09:24
@V.K, a product is not flawed just because it doesn't behave as you wish it would. The behavior is to threat an Empty/Blank cell as 0, which is perfectly reasonable since a mathematical operation can't handle an undefined number. And no, `PRODUCT(A1, 5)` doesn't treat your BLANK cell `A1` as `1`. It returns 5 because `A1` is BLANK and skipped, which leaves `5`. — michael, Aug 13 '16 at 11:01
@michael, Yes, it is flawed in its design because it fails https://en.wikipedia.org/wiki/Principle_of_least_astonishment. if sum() is analogy to operation +, then product() is generally understood as analogy to operation *. But not in Excel! If you you have cells A1=1 and A2 is blank and calculate A1*A2, you get 0. But if you calculate PRODUCT(A1:A2), you get 1. I am astonished! Maybe you are not, but I am. While with SUM() and + you get the same result 1. I do not say it is a bug, I only say it is a bad design. Actually the whole idea of treating blank cells as valid values is crazy. — HiFile.app - best file manager, Aug 13 '16 at 11:32
@Anastasiya-Romanova秀 Yes, the title might have been misleading, because it is so short so I cannot put all the details there. But you also need to read the text not just title. And I think from the text of the question it follows that I want to multiply each value in a range by a constant rather than mutiply all the values and return the result. However I edited the title to be more clear. :) — HiFile.app - best file manager, Aug 13 '16 at 11:39

score 5 · Accepted Answer · answered Aug 13 '16 at 09:31

5

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

answered Aug 13 '16 at 09:31

Robin Mackenzie

18,801
7
38
56

Thank you, it works fine! I was considering something like that before but I did not expect that "" would be evaluated as a blank cell but I would expect this to be a text of zero length. This is also a bit surprising. For example if you write formula ="" to cell A1 and then you copy this cell and paste it as value to cell A2, then cell A2 is not blank (you can test it with function ISBLANK()) but it is a text of zero length. – HiFile.app - best file manager Aug 13 '16 at 11:49
Cant try it, but should work without named range `[IF(ISBLANK(A1:C3),A1:C3,A1:C3*2)]` or the uglyer `Evaluate(Replace("IF(ISBLANK( ), , *2)", " ", r.Address(0, 0)))` – Slai Aug 13 '16 at 11:57

score 1 · Answer 2 · answered Aug 13 '16 at 14:11

I know you have an accepted answer, but for whatever it's worth it turns out you don't have to name the range. And in case the cells in the range contain text, then this one-line code works fine

Sub MultiplyRangeByConstant()
    [A1:C3] = [IF(ISBLANK(A1:C3),"",IF(ISTEXT(A1:C3),A1:C3,2*A1:C3))]
End Sub

Slai · Answer 3 · 2016-08-13T15:29:44.327

1

if there are formulas or anything else in the range:

'[a1:b3] = [{"=1","a";2,"=0/0";"",3}]
[a1:b3] = [if(a1:b3="","",if(isNumber(a1:b3),a1:b3*2,a1:b3))]

or to ignore the formulas, the good old PasteSpecial

Set temp = [c1].EntireRow.Find("") ' any blank cell that is not in the range
temp.Value = 2
temp.Copy
[a1:b3].SpecialCells(xlCellTypeConstants).PasteSpecial , Operation:=xlMultiply
temp.Value = ""

edited Aug 13 '16 at 15:29

answered Aug 13 '16 at 15:09

Slai

22,144
5
45
53

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

3 Answers3

Linked