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.
[Solved] Merge cells in a row
[Solved] Merge cells in a row
Last edited by stigvoll on Fri Jan 29, 2021 3:32 pm, edited 1 time in total.
Apache OpenOffice 4.1.5
macOS 11.1
macOS 11.1
Re: Merge cells in a row
Merge B1:B2 and copy.
Select B3:B100 and paste.
Select B3:B100 and paste.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Merge cells in a row
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.
Apache OpenOffice 4.1.5
macOS 11.1
macOS 11.1
Re: Merge cells in a row
=IF(MOD(ROW();2);B1&" "&B2;"")
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Merge cells in a row
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
Apache OpenOffice 4.1.5
macOS 11.1
macOS 11.1
Re: Merge cells in a row
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.
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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Merge cells in a row
Aha! Splendid! Thank you, Sir, for helping out a complete newbie
Apache OpenOffice 4.1.5
macOS 11.1
macOS 11.1