CODEWITHSUNDEEP
excelexcel-formula
MAK
MAK

Reputation: 7270

Excel : Drag cell with one cell gap

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?

Upvotes: 0

Views: 583

Answers (2)

user4039065
user4039065

Reputation:

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

Upvotes: 1

Mrig
Mrig

Reputation: 11712

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,"")

enter image description here

Upvotes: 2

Related Questions