how to keep Text inside merged cells in vba after any iteration

[sam1266]

Hi guys,

I have encountered an issue with keeping the text inside the merged cells. This is my code for merging cells: Range(Cells(xx + 5, xyz), Cells(xx + 30, xyz + 2)).Merge.

The cell references (xx and xyz) are variable in the x and y direction. After the next xyz, the text I entered disappears.

do you have any solution for me ?

 

[Joe4]

Welcome to the Board!

If you have VBA, I would STRONGLY encourage you NOT to use Merge Cells!
Merge Cells are an abomination, probably one of the worst features of Excel.
They are so bad that most serious Excel programmers refuse to use them.
They cause all sort of issues for things like VBA, sorting, and other functions.

For merging cells across single rows, you can use the "Center Across Selection" formatting feature instead of merged cells.
It gives the same visual effect as merged cells, but without all the issues.
That method is described here: Tom’s Tutorials For Excel: Using Center Across Selection Instead of Merging Cells – Tom Urtis

 

[sam1266]

Welcome to the Board!

If you have VBA, I would STRONGLY encourage you NOT to use Merge Cells!
Merge Cells are an abomination, probably one of the worst features of Excel.
They are so bad that most serious Excel programmers refuse to use them.
They cause all sort of issues for things like VBA, sorting, and other functions.

For merging cells across single rows, you can use the "Center Across Selection" formatting feature instead of merged cells.
It gives the same visual effect as merged cells, but without all the issues.
That method is described here: Tom’s Tutorials For Excel: Using Center Across Selection Instead of Merging Cells – Tom Urtis

thank you, do you have the code in vba for it ? i like to try it

 

[jolivanes]

There must be a better way but this does the trick.

Sub Change_Merged_To_Center_Across()
Dim c As Range
For Each c In ActiveSheet.UsedRange
With c
 If .MergeCells Then
    With .MergeArea
        .UnMerge
        .HorizontalAlignment = xlCenterAcrossSelection
    End With
 End If
End With
Next c
End Sub

 

[Joe4]

thank you, do you have the code in vba for it ? i like to try it

Just turn on your Macro Recorder, and record yourself doing it manually.
That will give you the VBA code you need to do it.

 

[sam1266]

Just turn on your Macro Recorder, and record yourself doing it manually.
That will give you the VBA code you need to do it.

this is my code :
Dim xx As Integer, zz As Integer, xyz As Integer, yy As Integer
xx = Application.WorksheetFunction.Max(Range("TreeData").Columns(4)) ' Number of columns in event tree range
'
Range("ALP999").Formula2R1C1 = "=SUM(IF(FREQUENCY(R[-70]C[1]:R[185]C[1],R[-70]C[1]:R[185]C[1])>0,1))"
zz = Range("ALP999").value ' Assign the value from the specified range to zz

For yy = 2 To zz
For xyz = 3 To (yy - 1) * 4 Step 4
Range(Cells(xx + 5, 1), Cells(xx + 30, 1)).Merge
Range(Cells(xx + 5, xyz), Cells(xx + 30, xyz + 2)).Merge

Next xyz

Next yy

 

[sam1266]

This is my code. Could you help me merge it with yours?"
Dim xx As Integer, zz As Integer, xyz As Integer, yy As Integer
xx = Application.WorksheetFunction.Max(Range("TreeData").Columns(4)) ' Number of columns in event tree range
'
Range("ALP999").Formula2R1C1 = "=SUM(IF(FREQUENCY(R[-70]C[1]:R[185]C[1],R[-70]C[1]:R[185]C[1])>0,1))"
zz = Range("ALP999").value ' Assign the value from the specified range to zz

For yy = 2 To zz
For xyz = 3 To (yy - 1) * 4 Step 4
Range(Cells(xx + 5, 1), Cells(xx + 30, 1)).Merge
Range(Cells(xx + 5, xyz), Cells(xx + 30, xyz + 2)).Merge

Next xyz

Next yy