How to merge every other row/cell in Excel?

If you have a list of data, and you want to combine every other row or cell of them as shown as below, do you know how to get it done? In Excel, you can use formula or VBA to merge every other row or cells.

Merge every other row/cell with formula in Excel

Merge every other row/cell with VBA

---

Merge every other row/cell with formula in Excel

---

Select a blank cell and type =OFFSET(A$1,(ROW()-1)*2,0)&" "&OFFSET(A$1,((ROW()-1)*2)+1,0) ( A$1 indicates the first data in your list, you can change it as you need), and press Enter key. Then drag the fill handle to fill the range you need to apply this formula. See screenshot:

---

Merge every other row/cell with VBA

If you are used to use VBA, you also can run the below VBA to merge every other row.

1. Hold ALT button and press F11 on the keyboard to open a Microsoft Visual Basic for Application window.

2. Click Insert > Module, and copy the VBA into the module.

VBA: Merge every other row/cell

Sub CombineCells()
'Updateby20140314
Dim rng As Range
Dim InputRng As Range, OutRng As Range
xTitleId = "KutoolsforExcel"
Set InputRng = Application.Selection
Set InputRng = Application.InputBox("Range :", xTitleId, InputRng.Address, Type:=8)
Set OutRng = Application.InputBox("Out put to (single cell):", xTitleId, Type:=8)
For i = 1 To InputRng.Rows.Count Step 2
    For j = 1 To InputRng.Columns.Count
        OutRng.Value = InputRng.Cells(i, j).Value & InputRng.Cells(i + 1, j).Value
        Set OutRng = OutRng.Offset(0, 1)
    Next
    Set OutRng = OutRng.Offset(1, (InputRng.Columns.Count * -1))
Next
End Sub         

 

3. Click Run to run the VBA code, and a KutoolsforExcel dialog pops up for you to select a range of cells that you want to merge the every other row. See screenshot:

4. Click OK, another dialog displays for you to select a single cell to put out the merged result. See screenshot:

5. Click OK, and you can see the result:

---

Relative Articles:

• Select every other or nth row in Excel
• Copy every other row
• Delete every other row