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.
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:
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: