Macro adds number to entire selection, how to avoid adding to blank cells?

**Butcher1** · 02-26-2014, 01:00 PM

I have recorded the below macro which will copy a value and the paste special and add it to the selection. The problem is that it is adding the value to blank cells when I do not want it to. Can this code be modified so that it doesn't add to blank cells. In the paste special dialog box I clicked skip blanks but this didn't change anything.

Sub increasebets()
'
' increasebets Macro
' This will send the cursor to the last completed spin, then down to the increase previous bet column for the next bet and add that to all the bets on the table.
'

'
    Range("F2").Select
    Selection.End(xlToRight).Select
    ActiveCell.Offset(7, 1).Range("A1").Select
    Selection.Copy
    Sheets("Bets").Select
    Union(Range( _
        "T20,T22,R22,P22,P24,R24,T24,O3:O25,Q3:Q25,S3:S25,V4:V22,X4:X22,AA4:AA15,P4,R4,T4,T6,R6,P6,P8,R8,T8,T10,R10,P10,P12,R12,T12,T14,R14,P14,P16" _
        ), Range("R16,T16,T18,R18,P18,P20,R20")).Select
    Range("T24").Activate
    Selection.PasteSpecial Paste:=xlPasteAllExceptBorders, Operation:=xlAdd, _
        SkipBlanks:=False, Transpose:=False
    Range("O1").Select
    Sheets("Spins").Select
    Range("F2").Select
End Sub

**Bernie Deitrick** · 02-26-2014, 03:05 PM

Change

Selection.PasteSpecial Paste:=xlPasteAllExceptBorders, Operation:=xlAdd, _
        SkipBlanks:=False, Transpose:=False

to this, if the cells have values and not formulas

Selection.SpecialCells(xlCellTypeConstants, 23).PasteSpecial Paste:=xlPasteAllExceptBorders, Operation:=xlAdd, _
        SkipBlanks:=False, Transpose:=False

or this, if the cells have formulas

Selection.SpecialCells(xlCellTypeFormulas, 23).PasteSpecial Paste:=xlPasteAllExceptBorders, Operation:=xlAdd, _
        SkipBlanks:=False, Transpose:=False

and if they are all values, you don't need to use all that selecting (though this is just a guess as to which sheet is active when the code is run)

    With Sheets("Bets")
    With Union(.Range( _
               "T20,T22,R22,P22,P24,R24,T24,O3:O25,Q3:Q25,S3:S25,V4:V22,X4:X22,AA4:AA15,P4,R4,T4,T6,R6,P6,P8,R8,T8,T10,R10,P10,P12,R12,T12,T14,R14,P14,P16" _
               ), .Range("R16,T16,T18,R18,P18,P20,R20")).SpecialCells(xlCellTypeConstants, 23)
        .Value = .Value + Sheets("Spins").Range("F2").End(xlToRight).Offset(7, 1).Value
    End With
End With