Calc Macro Merge Cells

I have a macro that requires user input to:
dispatcher.executeDispatch(document, “.uno:ToggleMergeCells”, “”, 0, Array())

The pop up shows 3 options. I would like code to provide an answer to the pop up.

Sub toggleMerge(oRange)
bM = oRange.getIsMerged()
oRange.merge(Not bM)
End Sub

Many thanks for your quick reply – sorry I could not get back to you as quickly.

I’m having trouble with that code; therefore, I must be over looking something.

Here’s an example of what I’m trying to do:

Sub Trial

document = ThisComponent.CurrentController.Frame

dispatcher = createUnoService(“”)

Doc = ThisComponent

Sheet = Doc.Sheets(1)

args1(0).Name = “ToPoint”

oRange = “$A$1:$D$1”

args1(0).Value = oRange

dispatcher.executeDispatch(document, “.uno:GoToCell”, “”, 0, args1())

Call toggleMerge(oRange)

End Sub

I always get an error with Sub toggleMerge(oRange) highlighted and

BASIC runtime error.

Object variable not set.

So I’m missing something. What is it?

Thanks again,


PS: I’ve got a work around. If the cell contents are in the first column of the range, dispatcher.executeDispatch(document, “.uno:ToggleMergeCells”, “”, 0, Array())

works without any user input. So I check the position of the cells content, and move them to the first column of the range. It’s messy with a lot of code but it works. I’d rather use toggleMerge(oRange) if I could.

because you pass a text instead of a range.

toggleMerge.ods (15.2 KB)

Many thanks for toggleMerge.ods, it was very helpful. FYI, when I used the macro code in My Macros I had to add Dim rg as Object, and Dim bM as Boolean. I could not find where they were specified.

Again, thanks.


Is there a way to do the merging with “Move the contents of the hidden cells into the first cell”?

The option dialog was added in 2016.

There is no way to pass the move option to XMergeable.