[Solved] Merge cells in a row

[stigvoll]

I'm looking for a macro than enables me to merge every two cells in a row.

Merge B1 and B2
Merge B3 and B4
Merge B5 and B6

... and so on.

I want to highlight the entire column from B1 to B100 and then have every two cells merged when running the macro.

[Villeroy]

Merge B1:B2 and copy.
Select B3:B100 and paste.

[stigvoll]

Ah. I wasn't clear enough. My bad. There's information in the upper cell wich I want to merge with the blank lower cell. Every other cell is blank and there's different information in the other cells, so copy and paste won't do me any good.

[Villeroy]

=IF(MOD(ROW();2);B1&" "&B2;"")

[stigvoll]

Thanks. I was kind of hoping for a code that would let me just highlight the cells and have them merged running a macro. I found this that merges cells in a row, but I don't know how to tweak it to merge cells in a column.

Code: Select all

Sub MergeTwoCells

   REM Tell the macro which sheet to work on - 0 is sheet1, 1, is sheet2, etc.
   Dim oSheet1 : oSheet1 = ThisComponent.Sheets.getByIndex(0)
   
   REM Find which cell (or range) is selected
   Dim oAddress As New com.sun.star.table.CellRangeAddress
   oAddress = ThisComponent.CurrentSelection.getRangeAddress
   
   REM Get the starting row and column
   Dim iRow As Integer : iRow = oAddress.startRow
   Dim iCol As Integer : iCol = oAddress.startColumn
   
   Dim oRange
   Dim oCell
   Dim s As String
   
   REM Run a loop to merge two cells for all the selected rows
   Do Until iRow > oAddress.endRow
   
      REM Merge the contents of the two cells
      oCell = oSheet1.getCellByPosition(iCol+1, iRow)
      s = oCell.getString()
   
      oCell = oSheet1.getCellByPosition(iCol, iRow)
      s = oCell.getString & s

      oCell.setString(s)
   
      REM Merge the cells
      oRange = oSheet1.getCellRangeByPosition(iCol, iRow, iCol+1,iRow)
   
      oRange.merge(True)
      iRow = iRow + 1
   Loop
End Sub

[Villeroy]

A spreadsheet is a simplified programming language.
Create the merging in some free range, say X1:X100.
Concatenate the text values in some other free range, say Y1:Y100.
Copy the text column and paste-special text values over the formatted range (Ctrl+Shift+V).
Finally, copy the resulting cells over B1:B100.

[stigvoll]

Aha! Splendid! Thank you, Sir, for helping out a complete newbie