Merging two cells and then repeating in adjacent columns for the same two rows

[copperlap]

In VBA I can merge two cells in one column after selecting both cells with the Refedit. But if I try to move to the next column and try to merge the same two rows under the same sub procedure, nothing happens. I've tried just about everthing I can think of including offset. Ultimately what I am trying to do is merge two rows in multiple columns but each column has to be merged separately. To get me started I did use the macro recorder in order to get some information. Selected two rows(10 & 11) in column A, did the format, merge and then selected the column B rows (10 & 11). Here is what I have so far.


Private Sub CommandButton1_Click()

Dim StrAddr As String

StrAddr = RefEdit1.Value

Range(StrAddr).MergeCells = True

End Sub


If I try the offset function, it will merge what ever offset I select but will not merge both the original selected rows and the offset rows.


Private Sub CommandButton1_Click()

Dim StrAddr As String

Dim UserRange1 As Range

StrAddr = RefEdit1.Value

Set UserRange1 = Range(StrAddr)

UserRange1.Offset(0, 0).MergeCells = True

UserRange1.Offset(0, 1).MergeCells = True

End Sub

 

[Luke M]

Please, please, please do not merge cells! As you are already starting to discover, it causes many problems, especially when trying to do things with VB. I'd recommend using the Horizontal alignement setting of "Center across selection". Gives the same appearance w/o all the headaches.

 

[copperlap]

Tried it, doesn't work. Each column has a border around each cell. "Center across selection" only works with the values inside the cells. I want the border to go around the outside of both cells in a column and center the value inside the entire cell. It's not that much trouble, just need to figure out how to move the selection to the next column, right now I can move the selection but it doesn't select both cells in the adjacent column.

 

[NARAYANK991]

Hi ,


Try this :

[pre]

Public Sub Merge_Cells()
Const MERGE_COLUMNS = 7    ' Number of column cells to be merged at a time
Const MERGE_ROWS = 8       ' Number of row cells to be merged at a time

With Selection
Number_of_Rows = .Rows.Count
Number_of_Columns = .Columns.Count
End With

If Number_of_Rows < MERGE_ROWS Then mr = Number_of_Rows Else mr = MERGE_ROWS
If Number_of_Columns < MERGE_COLUMNS Then mc = Number_of_Columns Else mc = MERGE_COLUMNS

If mr < 2 And mc < 2 Then Exit Sub

With Selection.Cells(1, 1)
For i = 0 To (Number_of_Rows  mr) - 1
For j = 0 To (Number_of_Columns  mc) - 1
k = j * 2 - 1
If k < 0 Then k = 0
l = i * mr - (mr - 1)
If l < 0 Then l = 0
.Offset(l, k).Resize(mr, mc).MergeCells = True
Next
Next
End With
End Sub

[/pre]
You can also download the blank workbook with this code here :


http://speedy.sh/3RnvQ/Merge-Cells.xlsm


Narayan

 

[copperlap]

This does the same thing as the code I provided. What I need is each column of two rows separately being merged, not the entire selection. When I use the RefEdit feature it is to get the correct 2 rows, once I have the correct rows I need to merge the same rows but separately in each column (total of 5 columns, not all next to each other). The code provided does give me some ideas. Thanks