Excel : Drag cell with one cell gap

Question

I have the formula written in the cell B1 and need to drag it down till the B100.

Example:

Formula for cell B1

=(A2 - A1)

Note: Now I need to drag cell B1 to B100 and need one cell blank in between like shown below:

Expected Result:

A       B
-----------
2       3
5
8       1    
9

Is that possible to format the drag option?

Answer 1

You can use:

=IF(MOD(ROW(),2)<>0,A2-A1,"")

As pointed out by @Jeeped, above formula can also be written as:

=IF(MOD(ROW(),2),A2-A1,"")

Answer 2

A typical VBA solution might be easily done with a Union method.

Sub werqtr()
    Dim i As Long, bs As Range

    With Worksheets("Sheet1")   'KNOW WHAT WORKSHEET YOU ARE ON!!!!!!
        Set bs = .Range("B1")
        For i = 3 To 100 Step 2
            Set bs = Union(bs, .Cells(i, "B"))
        Next i
        bs.Formula = "=A2-A1"
    End With

End Sub