+ Reply to Thread
Results 1 to 4 of 4

Merge similar cells in column and adjacent columns

Hybrid View

  1. #1
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,892

    Merge similar cells in column and adjacent columns

    Hello everyone
    I have a code that merge similar cells in column
    Sub Merge_Similar_Cells()
        Dim WorkRng As Range
        Dim Rng As Range
        Dim xRows As Integer
        Dim I As Integer
        Dim J As Integer
    
    
        Application.ScreenUpdating = False
        Application.DisplayAlerts = False
            Set WorkRng = Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row)
        
            xRows = WorkRng.Rows.Count
            For Each Rng In WorkRng.Columns
                For I = 1 To xRows - 1
                    For J = I + 1 To xRows
                        If Rng.Cells(I, 1).Value <> Rng.Cells(J, 1).Value Then
                            Exit For
                        End If
                    Next J
                    WorkRng.Parent.Range(Rng.Cells(I, 1), Rng.Cells(J - 1, 1)).Merge
                    
                    I = J - 1
                Next I
            Next Rng
        Application.DisplayAlerts = True
        Application.ScreenUpdating = True
    End Sub
    I need to merge two more adjacent columns but depending on the similar values in first column
    The Columns A / B / C are the main data and I have put the expected output (desired results) in Columns F / G / H (Just for clarify the issue)
    But I need the working code to deal with columns A / B / C

    Hope it is clear
    Thanks advanced for help
    < ----- Please click the little star * next to add reputation if my post helps you
    Visit Forum : From Here

  2. #2
    Forum Expert millz's Avatar
    Join Date
    08-14-2013
    Location
    Singapore
    MS-Off Ver
    Excel, Access 2016
    Posts
    1,694

    Re: Merge similar cells in column and adjacent columns

    Try this:
    Sub Merge_Similar_Cells()
        Dim WorkRng As Range
        Dim Rng As Range
        Dim xRows As Integer
        Dim I As Integer
        Dim J As Integer, b As Boolean, K As Integer
    
    
        Application.ScreenUpdating = False
        Application.DisplayAlerts = False
            Set WorkRng = Range("A2:C" & Cells(Rows.Count, 1).End(xlUp).Row)
        
            xRows = WorkRng.Rows.Count
            For Each Rng In WorkRng.Columns
                For I = 1 To xRows - 1
                    For J = I + 1 To xRows
                        b = False
                        For K = Rng.Column To 1 Step -1
                            If Rng.Cells(I, 1).Offset(, K - Rng.Column).MergeArea.Cells(1, 1).Value <> Rng.Cells(J, 1).Offset(, K - Rng.Column).MergeArea.Cells(1, 1).Value Then b = True
                        Next
                        If b Then Exit For
                    Next J
                    WorkRng.Parent.Range(Rng.Cells(I, 1), Rng.Cells(J - 1, 1)).Merge
                    
                    I = J - 1
                Next I
            Next Rng
        Application.DisplayAlerts = True
        Application.ScreenUpdating = True
    End Sub
    多么想要告诉你 我好喜欢你

  3. #3
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,892

    Re: Merge similar cells in column and adjacent columns

    Really awesome and wonderful Mr. Milz
    Exactly as I need
    Thank you very much for great help
    Regards

  4. #4
    Forum Expert millz's Avatar
    Join Date
    08-14-2013
    Location
    Singapore
    MS-Off Ver
    Excel, Access 2016
    Posts
    1,694

    Re: Merge similar cells in column and adjacent columns

    You are very welcome

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Merge similar data in a nother column
    By YasserKhalil in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-24-2014, 06:11 PM
  2. [SOLVED] Merge cells in corresponding column based on similar values
    By Niclal in forum Excel Programming / VBA / Macros
    Replies: 27
    Last Post: 12-22-2014, 10:54 AM
  3. Need to merge columns with similar locations
    By aleanboy in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-08-2014, 04:25 PM
  4. Replies: 0
    Last Post: 07-24-2013, 11:13 AM
  5. Merge data from cells on 2nd columns in which the cells of 1st column repeat
    By jagke in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 01-14-2013, 01:06 PM
  6. Replies: 6
    Last Post: 12-05-2009, 10:07 AM
  7. MATCH *similar* strings and return adjacent column value
    By mzerega in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-22-2009, 07:36 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1