CODEWITHSUNDEEP
excelmergevba
user2742164
user2742164

Reputation: 1

Macro for merging cells in multiple columns across the same row

I am creating a kind of database if you will and i have multiple columns in the one row that need to be merged and am wondering if it's possible to do this with a macro.

To make it even more difficult the amount of cells to merge isn't constant. IE:

  1 A   B   C   MARY JO         50  main office     admin

  2 A   B   C   MARY JO         50  main office     admin

  3 A   B   C   MARY JO         50  main office     admin

  1 A   B   C   JOHN DOE        60  OTHER office    driver

  2 A   B   C   JOHN DOE        60  OTHER office    driver

Merge rows 1-3 and 4-5 is the desired outcome. Hard to explain without ability to post picture but column A and Z onwards want UNMERGED as each individual row has information displayed in these columns.

WHAT IT LOOKS LIKE NOW:

DESIRED FINAL OUTCOME:

Upvotes: 0

Views: 4669

Answers (3)

sbanders
sbanders

Reputation: 853

Try something like this:

Private Sub MergeTheseCellsToMakeSomethingLikeADatabase()
    'Crucial Line Below
    Application.DisplayAlerts = False
    Dim i%
    With ThisWorkbook.Sheets("Sheet1")
       For i = .UsedRange.Rows.Count to 2 Step -1
          If .Cells(i,4).Value = .Cells(i-1,4).Value Then
             .Range(.Cells(i,2),.Cells(i-1,2)).Merge
             .Range(.Cells(i,3),.Cells(i-1,3)).Merge
             .... 
             'Do this for all rows that are relevant or just have another for loop to cycle
             'Through the rows that you want merged      
          End If
       Next
    End With
End Sub

Upvotes: 1

Dave Sexton
Dave Sexton

Reputation: 11188

Are you trying to remove duplicate rows? If so highlight your data range and then from the menu select Data. On the Data ribbon go to the Sort and Filter section and click on advanced. In the advanced filter dialog select 'Copy to another location', make sure the List range has selected you data, in the copy to box select where you want your filtered list to go and then tick the box for unique records only. Click OK and job done.

Upvotes: 0

wingyip
wingyip

Reputation: 3536

Why don't you do this with formulae on another worksheet to create the desired outcome - macros are likely not needed unless you are trying to automate the entire process. If this is just a one-off then macros are definitely overkill.

Create coluumns with the desired combination of cells simply by creatign aformula like:

=A1&A3 etc

& is used instead of + to join text cells together (concatenate)

Copy that formuala down for each column and done... almost...

Now you can worry about merging rows

Wing

Upvotes: 0

Related Questions