Excel paste special to range but skip blanks

I want to divide the range B116 to L130 by 1M. Some cells in this range are blank. Using the following code results in zeros for the cells with blanks. Is there a way to do this without selecting each cell to determine if its blank and if not then use the paste special code?

    Range("B3").FormulaR1C1 = "1000000"
    Range("B3").Select
    Selection.Copy
    Range("B116:L130").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlDivide, _
        SkipBlanks:=True, Transpose:=False
ttbengineerAsked:
Who is Participating?
 
Wayne Taylor (webtubbs)Commented:
Use SpecialCells (Constants) to specify non-blank cells.

    Range("B3").Value = "1000000"
    Range("B3").Copy
    Range("B116:L130").SpecialCells(xlCellTypeConstants).PasteSpecial Operation:=xlDivide

Open in new window


If they are formulas instead of constants, use xlCellTypeFormulas instead.

Wayne
0
 
ttbengineerAuthor Commented:
Thank you Wayne. That worked just like I wanted. I appreciate the help!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.