• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 419
  • Last Modified:

Merge cells if equal

Hi,
Is it possible to write code or enter a formula that would look down column A and merge the cells that were equal? (there are no empty cells)
0
Stephen Byrom
Asked:
Stephen Byrom
3 Solutions
 
NBVCCommented:
Do you mean you want to end up with Unique Values Only?

If so,try selecting the column of values and go to Data|Remove Duplicates and click Ok.
0
 
jboddyCommented:
Can you clarify what you mean by "merge"?
0
 
Stephen ByromWarehouse/ShippingAuthor Commented:
Thanks for the pointer, but that contracts the range.
I also tried it using vba with
ActiveSheet.Range("A4:A500").RemoveDuplicates
but that did the same.
I just wanted to clear the cells that were the same. It would be an added bonus if after removing the duplicates the cells could then be merged.
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
Stephen ByromWarehouse/ShippingAuthor Commented:
by merge I mean that if for example cells a4 to a10 are the same then a4 to a10 would be merged then continue on down to A500 and merging duplicate cells.
a4 to a10 may be the same so merge, a11 to a26 may be the same so merge, etc.
Sorry I am about to embark on a new workbook and the idea came to me to try this.
column A would have numbers lots of them would be the same as they are populated from input from a userform. Columns B through I would also be populated through that form but all the data would be different so I wanted to keep those rows and just merge column A.
0
 
NBVCCommented:
You can use Conditional formatting so that the duplicates are coloured as per the background (i.e. white font) so that they are not visible.  This is better than merging cells as that is not recommended, especially if you are going to be referencing them with formulas.

To use conditional formatting, select the range, say A4:A500, then go to Home|Conditonal formatting, then select "use a formula to determine which cells to format", then enter formula:

=COUNTIF($A$4:$A4,$A4)>1

then click Format and choose Font tab to change font to white.

Click Ok and again to finish.
0
 
KrukanensCommented:
'Try this one:

Sub Merge()

    Dim sPreviousField As String
    Dim i As Integer
   
    i = 1
    While Not IsEmpty(Range("A" & i).Value)
       
        If i <> 1 And Range("A" & i).Value = sPreviousField Then
            Application.DisplayAlerts = False
            Range("A" & (i - 1) & ":A" & i).Merge
            Application.DisplayAlerts = True
        Else
            sPreviousField = Range("A" & i).Value
        End If
       
        i = i + 1
    Wend
   
End Sub
0
 
Saqib Husain, SyedEngineerCommented:
Try this

Sub mergesame()
Dim srw As Long
Dim erw As Long
    Application.DisplayAlerts = False
    srw = 1
    Do While Cells(srw, 1) <> ""
        erw = srw
        Do While Cells(srw, 1) = Cells(erw, 1)
            erw = erw + 1
        Loop
        erw = erw - 1
        Range(Cells(srw, 1), Cells(erw, 1)).Merge
        srw = erw + 1
    Loop
    Application.DisplayAlerts = True
End Sub
0
 
Stephen ByromWarehouse/ShippingAuthor Commented:
Wow!
Thanks for all the positive solutions.
They all do the trick but ssaqibh gets the lion's share as it executes really quick.

Thanks to all for your time
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now