Shalter,

Suggested is that you try the following experimentally.
It will require extensive testing to ensure it doesn't
corrupt data under some circumstances. Experiment on a
copy of the data. You cannot undo a macro unlike what
you're used to.

You didn't say that you needed to delete the rows made
empty by this process in order to compress the data so I
left it as is. A suggested alternative is to not merge
the cells but to:
1) Count the number of same Item numbers in column A
2) Record the result to to column C
3) Delete the rows containing the duplicates

From viewing your data, it was apparent that all repeating
values are contiguous. The code therefore assumes that
this will always be the case.

Sub ConsolidateData()
Dim i As Integer, ii As Integer, iii As Integer
Dim NumRows As Integer
NumRows = Range("A65536").End(xlUp).Row
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Columns("A:B").VerticalAlignment = xlVAlignTop
For i = 3 To NumRows + 3
For ii = i + 1 To NumRows + 3
If Trim(Cells(ii, 1)) = Trim(Cells(i, 1)) Then
iii = iii + 1
Else
If iii 0 Then
Range(Cells(i, 1), Cells(ii - 1, 1)).MergeCells = True
Range(Cells(i, 2), Cells(ii - 1, 2)).MergeCells = True
End If
Cells(i, 3) = iii + 1
i = ii - 1
iii = 0
Exit For
End If
Next ii
Next i
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

To repeat the above, experiment rigorously before using.

Regards,
Greg


-----Original Message-----
I had a reuqest to create a macro for excel to search for
and merge
cells that contain the same text into one cell with a
value in the next
column of how many cells were merged. I don't do a lot of
macro
programming, so this was a little beyond me.

Can anyone help? Thanks in advance.

Neil.



------------------------------------------------
Message posted from http://www.ExcelTip.com/

-- View and post Excel related usenet messages directly
from http://www.ExcelTip.com/forum
-- Hundreds of free MS Excel tips, tricks and solutions
at http://www.ExcelTip.com/
------------------------------------------------
.