Finding duplicates and merging them

jrnyman · Mar 13th, 2002, 05:48 AM

I have a column where many of the names will be repeated consecutively (after sorting). I want to use VBA to find a set of replicates and merge them into one cell. Any idea?

Derek · Mar 13th, 2002, 06:59 AM

Hi jrnyman

You could try this code in a macro:

Application.DisplayAlerts = False
For Each Cell In Selection
If ActiveCell.Offset(1, 0).Value = ActiveCell.Value Then
Range(ActiveCell, ActiveCell.Offset(1, 0)).Merge
Else
ActiveCell.Offset(1, 0).Select
End If
Next
End Sub

This seems to do what you want, but test it out first. Be careful because it will merge blank cells if they are in your selection. Particularly make sure the last cell in your selection is not blank (or it will merge right down the column). Don't forget you can't use the undo button with a macro.

Hope this helps
regards
Derek

[ This Message was edited by: Derek on 2002-03-13 06:00 ]

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 13th, 2002, 05:48 AM	#1
jrnyman Board Regular Join Date: Mar 2002 Location: Boston, MA Posts: 105	I have a column where many of the names will be repeated consecutively (after sorting). I want to use VBA to find a set of replicates and merge them into one cell. Any idea?

Mar 13th, 2002, 06:59 AM	#2
Derek Board Regular Join Date: Feb 2002 Location: Perth Australia Posts: 1,567	Hi jrnyman You could try this code in a macro: Application.DisplayAlerts = False For Each Cell In Selection If ActiveCell.Offset(1, 0).Value = ActiveCell.Value Then Range(ActiveCell, ActiveCell.Offset(1, 0)).Merge Else ActiveCell.Offset(1, 0).Select End If Next End Sub This seems to do what you want, but test it out first. Be careful because it will merge blank cells if they are in your selection. Particularly make sure the last cell in your selection is not blank (or it will merge right down the column). Don't forget you can't use the undo button with a macro. Hope this helps regards Derek [ This Message was edited by: Derek on 2002-03-13 06:00 ]