VBA Merge Help

adamnc

New Member
Joined
Sep 18, 2020
Messages
1
Office Version
  1. 2013
Platform
  1. Windows
Hi guys,

hope everyone is well :)

I was hoping someone could help me? I found a useful macro on here and I've een trying to tweak it but to no avail, and it's driving me nuts! The macro merged cells from the first cell containing a value, all the way down to the last empty cell in columns B & C. I'm trying to change it to complete the same merge for columns B, C & D. I would ideally like this to cover A, B & C but I don't mind having to insert a blank column for the sake of the code. No matter what I change, I can't seem to include the third column!

If whoever is brainy and kind enough to help me, as I am a complete beginner with VBA, would you mind pointing me in the right direction of how to change this? :)

Code below.
VBA Code:
With ws
    lngrow = Range("D" & .Rows.Count).End(xlUp).Row
    Set rng = Range(.Cells(2, 4), .Cells(lngrow, 4))
    For Each cell In rng
Debug.Print cell.Value
        If cell.Row = 2 Then
            Set rngMERGE = cell.Offset(, -1)
            intMERGE = cell.Row
        Else
            If Not cell.Row = lngrow Then
                If Not cell.Offset(, -1).Value = "" And intMERGE < cell.Row Then
                    Set rngMERGE = Range(.Cells(rngMERGE.Row, cell.Offset(, -1) _
                        .Column), .Cells(cell.Offset(-1).Row, cell. _
                        Offset(, -1).Column))
                    Set rngMERGEA = rngMERGE.Offset(, -1)
                    rngMERGE.Select
                    With rngMERGE
                        .MergeCells = True
                        .VerticalAlignment = xlCenter
                    End With
                    rngMERGEA.Select
                    With rngMERGEA
                        .MergeCells = True
                        .VerticalAlignment = xlCenter
                    End With
                    Set rngMERGE = cell.Offset(, -1)
                    intMERGE = cell.Row
                End If
            Else
                If cell.Offset(, -1).Value = "" Then
                    Set rngMERGE = Range(.Cells(rngMERGE.Row, cell.Offset(, -1) _
                        .Column), .Cells(cell.Row, cell. _
                        Offset(, -1).Column))
                    Set rngMERGEA = rngMERGE.Offset(, -1)
                    rngMERGE.Select
                    With rngMERGE
                        .MergeCells = True
                        .VerticalAlignment = xlCenter
                    End With
                    rngMERGEA.Select
                    With rngMERGEA
                        .MergeCells = True
                        .VerticalAlignment = xlCenter
                    End With
                End If
            End If
        End If
    Next cell
    End With
    End Sub




Thank you in advance! :)
 
Last edited by a moderator:

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,972
Office Version
  1. 365
Platform
  1. Windows
Welcome to the Board!

I think it would be helfpul is you post a sample of the data you are working with, and what you would like the output to look like.
There is a tool here that will allow you to post screen images: XL2BB - Excel Range to BBCode
 

Watch MrExcel Video

Forum statistics

Threads
1,123,206
Messages
5,600,313
Members
414,375
Latest member
Onmyown

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top