Merging every three rows

[marrick13]

I want to write a macro that will merge every three rows at a time and center the data in an Excel table. This should be done for three columns, but the macro can just be run separately for each column. The tables can vary in length but could consist of over 100 rows. They would typically look like this:

Row

3 RC628054E
4
5
6 RC628054F
7
8
9 RC628054G
10
11
12 RC628054I
13
14
15 RC628752G
16
17
18 RC628054M
19
20
21 RC628752H

...and so on.

So the code should merge rows 3-5, 6-8, 9-11, 12-14, 15-17, 18-20, etc, and center each merged cell (horizontal and vertical).

I've searched this site and others for code that I can adapt, but don't know Excel VBA well enough to pick out what will do the job. If someone can help out, that would be great.

 

[RSXchin]

the code below will work if you are merging Column A cells, and starts with cell A3. if you have any data in cell A4 or A5 (or any other cells you are merging) you'll get an error message that you have to click "ok" to everytime. It's annoying, if anyone can make this better... awesome.

Sub merge()
'
' merge Macro
'
'
Dim lastrow As Long, i As Long
lastrow = Cells(Rows.Count, 1).End(xlUp).Row
For i = 3 To lastrow Step 3
Range("A" & i, "A" & i + 2).Select
Selection.merge
Next i
End Sub

 

[MrKowz]

This should work for you.

Public Sub Merge3()
Dim i    As Long, _
    LR   As Long
 
LR = Range("A" & Rows.Count).End(xlUp).Row
 
With Application
   .ScreenUpdating = False
   .DisplayAlerts = False
End With
 
For i = 3 To LR Step 3
   With Range(Cells(i, 1), Cells(i + 2, 1))
      .Merge
      .HorizontalAlignment = xlCenter
      .VerticalAlignment = xlCenter
   End With
Next i
 
With Application
   .ScreenUpdating = True
   .DisplayAlerts = True
End With
 
End Sub

Note to rsxchin - If you use Application.DisplayAlerts = False, you can supress all alert boxes that pop up.

 

[RSXchin]

what is wrong with the code I put up? Is it too inefficient?

 

[marrick13]

Actually, both code modules work fine -- on column A. I was hoping I could also use the same code on columns B and C, but this code works only on column A. I was trying to keep it simple and flexible. Now I cannot determine why it merges only column A rows. So how can I get it to merge in the same pattern in columns B and C?

 

[MrKowz]

Here ya go - this will bring up an inputbox so that you can tell it what Column to perform the merge. Do one column at a time

Public Sub Merge3()
Dim i          As Long, _
    LR         As Long, _
    ColMerge   As String
    
ColMerge = InputBox("What column do you want to perform the merge on?")
LR = Range(ColMerge & Rows.Count).End(xlUp).Row
With Application
   .ScreenUpdating = False
   .DisplayAlerts = False
End With
For i = 3 To LR Step 3
   With Range(Cells(i, ColMerge), Cells(i + 2, ColMerge))
      .Merge
      .HorizontalAlignment = xlCenter
      .VerticalAlignment = xlCenter
   End With
Next i
With Application
   .ScreenUpdating = True
   .DisplayAlerts = True
End With
End Sub

 

[RSXchin]

Find every spot that my code says "a" and replace it with whatever. Not gonna lie, I don't understand his code at all

 

[John Davis]

Actually, both code modules work fine -- on column A. I was hoping I could also use the same code on columns B and C, but this code works only on column A. I was trying to keep it simple and flexible. Now I cannot determine why it merges only column A rows. So how can I get it to merge in the same pattern in columns B and C?

In case you wanted all three columns merged together:

Sub Marrick()
'
Dim i As Long
Dim lr As Long

lr = Cells(Rows.Count, 1).End(xlUp).Row

Application.DisplayAlerts = False

For i = 3 To lr Step 3

    Range(Cells(i, 1), Cells(i, 3)).Resize(3).Select
'
       With Selection
            .HorizontalAlignment = xlCenter
            .VerticalAlignment = xlCenter
            .MergeCells = True
       End With

Next i

Application.DisplayAlerts = True

End Sub

 

[marrick13]

MrKowz, your code is GREAT! The cursor doesn't need to be anywhere in particular and it merges perfectly. Thanks you SO much - this is a big help.

John Davis-thanks, but your code doesn't do anything. I ran it with the cursor both in the table and out and it made no difference. It's probably missing something very simple. No matter, though, because it's no problem running the MrKowz code 3 times - the fact fact of having code do this saves all the time of doing this manually. And actually, there is insert code I run before this. The issue is one of copying columns from another file into an Excel file that is both protected and merged, so I can't unmerge anything and am left with either manually inserting rows and then merging them -- or running this code to achieve the same.

Gentlemen, thank so much for your generous (and speedy) help. I really appreciate your efforts - and won't hesitate to ask for more when needed in the future!

 

[denmarie84]

Hello,

How would you modify this code so that it merges every 2 cells instead of 3? It's probably an easy fix, but I'm not sure what it is!

Thanks!