[Solved] Merge cells in a row

Creating a macro - Writing a Script - Using the API

[Solved] Merge cells in a row

Postby stigvoll » Fri Jan 29, 2021 12:04 pm

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.
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
stigvoll
 
Posts: 4
Joined: Fri Jan 29, 2021 11:55 am

Re: Merge cells in a row

Postby Villeroy » Fri Jan 29, 2021 2:33 pm

Merge B1:B2 and copy.
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
User avatar
Villeroy
Volunteer
 
Posts: 29552
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Merge cells in a row

Postby stigvoll » Fri Jan 29, 2021 2:37 pm

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
stigvoll
 
Posts: 4
Joined: Fri Jan 29, 2021 11:55 am

Re: Merge cells in a row

Postby Villeroy » Fri Jan 29, 2021 2:41 pm

=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
User avatar
Villeroy
Volunteer
 
Posts: 29552
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Merge cells in a row

Postby stigvoll » Fri Jan 29, 2021 2:50 pm

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   Expand viewCollapse view
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
stigvoll
 
Posts: 4
Joined: Fri Jan 29, 2021 11:55 am

Re: Merge cells in a row

Postby Villeroy » Fri Jan 29, 2021 3:13 pm

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.
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
User avatar
Villeroy
Volunteer
 
Posts: 29552
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Merge cells in a row

Postby stigvoll » Fri Jan 29, 2021 3:31 pm

Aha! Splendid! Thank you, Sir, for helping out a complete newbie :-)
Apache OpenOffice 4.1.5
macOS 11.1
stigvoll
 
Posts: 4
Joined: Fri Jan 29, 2021 11:55 am


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 2 guests