[SOLVED] Macro for Merge cells

[V1ce]

Hello,

I try to merge identical cells in 1 column with a macro but with my code can just merge 2 cells because i compare the current cell with the previous but if the previous is already merged that's don't work because when cells are merged the position of the cell is at the first cell of the range. See my code.

Code: Select all

seance=""
do while MaFeuille.getCellByPosition(x,0).string <>""

' here i put some text value on the column A
    cellu = newfeuille.getCellByPosition(0,x)
    cellu.string = MaFeuille.getCellByPosition(x,0).GetString

' here i compare the value with the previous cell and merge if its equal, it works but not after the 2nd because the merged cell is the first cell of the range
    if cellu.string = seance then
        y=x-1	
        plage = newfeuille.getCellRangeByPosition( 0, y, 0, x)
        plage.Merge( True )		
    Endif

seance = MaFeuille.getCellByPosition(x,0).GetString
x=x+1
loop

Thanks for your help

[Villeroy]

Are there constant values or formulas in column A?

[V1ce]

Just text

[Villeroy]

Code: Select all

Sub Main
	sh = ThisComponent.Sheets.getByIndex(0)
	cell = sh.getCellByPosition(0,0)
	ca = cell.getCellAddress()
	urg = getCurrentRegion(cell) 'region of adjacent non-blanks around the given cell or range
	col = urg.Columns(0) 'first column of that
	'mri col ' you don't have the MRI extension? Why not?
	do
		diff = col.queryColumnDifferences(ca)'collection of ranges that differ from the cell at given address
		if diff.getCount()>0 then
			diff2 = diff.getByIndex(0)'first range of column differences
			rg = sh.getCellRangeByPosition(0, ca.Row, 0, diff2.RangeAddress.StartRow -1)
			rg.merge(true)
			cell = diff2.getCellByPosition(0,0)
			ca = cell.getCellAddress()
			col = diff2
		else
			exit do
		endif
	loop
End Sub

[Villeroy]

Oh, forgot to add this:

Code: Select all

Function getCurrentRegion(oRange)
Dim oCursor
   oCursor = oRange.getSpreadSheet.createCursorByRange(oRange)
   oCursor.collapseToCurrentRegion
   getCurrentRegion = oCursor
End Function

[V1ce]

Thank you this work!! But in my case i have some blank cells in the column and this break the function and the last identical cell group isn't merged.

How to permit blank cells and don't merge them?

[Villeroy]

see later attachment

[Villeroy]

I have improved the macro and attached my test document

[V1ce]

Hi,

Thank you for your time but the 2nd script isnt really what i need. Your First script was really great but just don't work if i have a blank line in the file. i joined you an exemple with your first script :

- Sheet 1 : With a blankline, this work but not on the last value to merge.
- Sheet 2 : Without blank line, all work perfectly, but i need the blank line :p
- sheet 3 : what i need.

Thanks

[Villeroy]

How about this? You can specify a cell address in the push button properties.

[V1ce]

I think i will choose the 1st solution, move the total cell and hide the cell between it and the cell region. But i have a problem i can't run your script with my real file, this line generate an error, in the old script i can determinate the sheet to work with the variable "sh" but here is different and the problem is here i think.

cell = ThisComponent.Sheets.getCellRangesByName(sTag)(0)

Thanks

[Villeroy]

Turn on form design mode (button #2 on form controls toolbar) and get the properties of the push button.
The push button has a property "Additional info". There I entered the address of a cell in the column to be merged, e.g. Feuille1.E5 merges cells in column E on Feuille1.
This way you can use the same macro with different sheets and even different columns.
The macro reads the cell address from the calling push button which gives the sheet and the column.
Then it determines the current region which is a rectangle of content cells surrounded by blank cells or the sheet border.

[V1ce]

Hi,

I can't use this because I have a sheet with a big table and I generate multiple sheets from this table so the button will just be in one sheet. The macro create a sheet like in the attachment and merge identical cells automatically. There is no way to use your 1st script and permit a blank line in the sheet? In the code i can put the first and last row for the zone to merge if needed.

[Villeroy]

Then you have to parameterize the macro and write a loop or a list of calls passing over the required data.

[V1ce]

Perfect this now work like i want. Many Thanks!