Merging specific cells(dynamic) and Suppress Excel's msgbox

[sebas1102]

Hi all,
I have an excel list that pulls out data from a master list.
from column A onwards, it pulls out the item's name("A"), Date of Production("B") and total quantity("C").
For an item with different production dates, the total quantity are the same. So, I first sort out items according to their names, and merge the total quantities together to 1 cell. ( for example"A1" to "A5" are of the same item apple, and "A6" to "A11" are all bananas) So I need to merge the cells of total quantities from C1 to C5 and B6 to B11 respectively.

2 problems cropped out: 1) I used record macro, but it cannot dynamically merge the cells if the number of items change.
[vba]
Range("C1:C5").Select
Selection.Merge
Range("C6:C11").Select
Selection.Merge
[/vba]

2) When I run the macro, a msgbox keeps popping out:
"selection contains multiple data values. merging cells will keep the left uppermost data only."

so i tried using [vba]on error resume next[/vba] , but it still does not suppress the popup.

Please help!

 

[Von Pookie]

That's because it's not an error

What you want is to set DisplayAlerts to False to stop the prompt from appearing.

Application.DisplayAlerts = False

Range("C1:C5").Merge
Range("C6:C11").Merge

Application.DisplayAlerts = True

 

[Norie]

sebas1102

Why do you want to merge in the first place?

All that will do is lose data.

 

[sebas1102]

sebas1102

Why do you want to merge in the first place?

All that will do is lose data.

I need to merge those cells because the data is the same and I do not want to confuse the user. Is it possible?

 

[sebas1102]

That's because it's not an error

What you want is to set DisplayAlerts to False to stop the prompt from appearing.

Application.DisplayAlerts = False

Range("C1:C5").Merge
Range("C6:C11").Merge

Application.DisplayAlerts = True

oh ok! haha thanks alot!

 

[Cindy Ellis]

Have you solved the other part of the problem (varying numbers of cells to be merged)?

 

[erik.van.geit]

Hi, Cindy Ellis,

try this

Option Explicit
Option Base 1

Sub merge_same_data()
'Erik Van Geit
'050802 0337
'merge cells with same contents in single column
'skip "singles" to make code faster

Dim rng As Range
Dim LR As Long
Dim i As Long
Dim j As Long
Dim k As Long
Dim Arr As Variant
Dim ArrItem As String
Dim ArrRowNumbers() As Variant
Dim HL As Integer 'highlight

Const col = 1
Const FR = 2

If Cells(Rows.Count, col) <> "" Then LR = Rows.Count Else LR = Cells(Rows.Count, col).End(xlUp).Row
Set rng = Range(Cells(FR, col), Cells(LR, col))

Arr = rng.Value
i = 1
j = 1
    Do
    ArrItem = Arr(i, 1)
    k = i
        On Error Resume Next 'avoids bug at the end of the loop "Arr(i, 1)" when i > UBound(arr)
        Do
        i = i + 1
        Loop While ArrItem = Arr(i, 1)
        On Error GoTo 0
            'If k <> i - 1 Then     'enable these line to skip singles
            ReDim Preserve ArrRowNumbers(j + 1)
            ArrRowNumbers(j) = k + FR - 1
            ArrRowNumbers(j + 1) = i - 1 + FR - 1
            j = j + 2
            'End If                 'enable these line to skip singles
    Loop While i < LR

  
With Application
.ScreenUpdating = False
.DisplayAlerts = False
End With

For i = 1 To j - 1 Step 2
Range(Cells(ArrRowNumbers(i), col), Cells(ArrRowNumbers(i + 1), col)).Merge
Next i
'For i = 1 To j - 1 Step 2
'HL = IIf(HL = 35, 36, 35)
'Range(Cells(ArrRowNumbers(i), col), Cells(ArrRowNumbers(i + 1), col)).Interior.ColorIndex = HL
'Next i

With Application
.ScreenUpdating = True
.DisplayAlerts = True
End With

End Sub

kind regards,
Erik

 

[erik.van.geit]

another thought on this
using conditional format, you could "hide" the duplicates

something like
select A2:A65536
menu format / conditional format
optio "formula"
=A2=A1
fontcolor = white

merging cells has major disadvantages: a lot of experts hate them