How to efficiently merge cells using a macro?

**Margot Vanlaet** · 08-25-2022, 05:45 AM

Hi everyone,

I am trying to develop an index, but in order to make correct calculations and pivot tables, I need to merge some cells. Doing this manually would take a lot of time, therefore I am looking for a more efficient way. I assume a Macro might help me out here, yet I do not really know how to get started.

A bit of context on the datasheet: The info in cells E until AB was used to calculate the numbers in cells AC, AD, AE and AF. This information was hided as these cells do not need to be merged.

I am in the need of a macro that:
- Merges the value in column A when the organization name is identical.
- Merges the information in columns AC, AD, AE and AF when it refers to the same organization in column A.

Can somebody help me out? Thank you in advance for the help!!

Before
Screenshot 2022-08-25 at 11.29.31.png

After
Screenshot 2022-08-25 at 11.48.40.png

crossposted on mrexcel

**Margot Vanlaet** · 08-25-2022, 05:51 AM

This is what should happen after the calculation, but done by a macro so that I can re-use it on larger filesAttachment 793336

**JEC.** · 08-25-2022, 05:53 AM

Do you have some expected outputs? I don't really understand right now.
I can't open the attachment in post 2. Could you combine that into your first file?

Also tell that it's crossposted on MrExcel...

**Margot Vanlaet** · 08-25-2022, 06:02 AM

Done, thank you for the tips

**Margot Vanlaet** · 08-25-2022, 06:03 AM

Done, thank you for the tips

**JEC.** · 08-25-2022, 06:42 AM

I strongly discourage to use merged cells but here a solution

Sub jec()
 Dim ar, i As Long, j As Long
 Set ar = Range("A1", Range("AF" & Rows.Count).End(xlUp))
 Application.DisplayAlerts = False
 j = ar.Rows.Count
 
 For i = j To 2 Step -1
   If ar(i, 1) <> ar(i - 1, 1) Then
     With ar(i, 1)
       .Resize(j - i + 1).Merge
       .Offset(, 28).Resize(j - i + 1).Merge
       .Offset(, 29).Resize(j - i + 1).Merge
       .Offset(, 30).Resize(j - i + 1).Merge
       .Offset(, 31).Resize(j - i + 1).Merge
       j = i - 1
     End With
  End If
 Next
 
 With ar
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlCenter
 End With
 
 Application.DisplayAlerts = True
End Sub

**Margot Vanlaet** · 08-25-2022, 07:23 AM

Would another method be more sufficient?

How to efficiently merge cells using a macro?

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

How to efficiently merge cells using a macro?

Re: How to efficiently merge cells using a macro?

Re: How to efficiently merge cells using a macro?

Re: How to efficiently merge cells using a macro?

Re: How to efficiently merge cells using a macro?

Re: How to efficiently merge cells using a macro?

Re: How to efficiently merge cells using a macro?

Thread Information

Users Browsing this Thread

Similar Threads

How to macro multiple lookups on over 500,000 rows of data efficiently?

Optimized macro to run more efficiently

Macro to run formulas more efficiently

[SOLVED] Macro to more efficiently copy/paste every 21 rows

[SOLVED] Can this macro be written more efficiently?

Merge cells data entry automation...can this be done faster/more efficiently?

Help to get Macro operating efficiently

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions