We help IT Professionals succeed at work.

Warning Message

Cartillo
Cartillo asked
on
Hi Experts,

I need Experts help. When I run this macro, warning message has been pop-up as "The selection contain multiple value". How to prevent this pop-up message?
Sub MergeCells()
Dim c As Range, firstaddress As String, rng1 As Range, rng2 As Range
Dim I As Long, asht As Worksheet
Set asht = ActiveSheet
For I = 1 To 5
    Sheets("Week" & I).Activate
    Set rng1 = Sheets("Week" & I).Range("B4:H291")
    With rng1
        Set c = .Find("*")
        If Not c Is Nothing Then
            firstaddress = c.Address
            Do
                If c.Offset(1) = "" Then
                    If c.End(xlDown).Row <> Rows.Count Then
                        Set rng2 = Range(c, c.End(xlDown).Offset(-1))
                    Else
                        Set rng2 = Range(c, Cells(rng1.Cells(rng1.Cells.Count).Row, c.Column))
                    End If
                    With rng2
                        .HorizontalAlignment = xlCenter
                        .VerticalAlignment = xlCenter
                        .WrapText = True
                        .MergeCells = True
                    End With
                End If
                Set c = .FindNext(c)
            Loop While Not c Is Nothing And c.Address <> firstaddress
        End If
    End With
    With rng1
        .Font.Size = 9
        .Font.Bold = True
        With .Borders(xlEdgeBottom)
            .LineStyle = xlContinuous
            .Color = RGB(100, 100, 100)
        End With
        With .Borders(xlEdgeRight)
            .LineStyle = xlContinuous
            .Color = RGB(100, 100, 100)
        End With
        With .Borders(xlInsideVertical)
            .LineStyle = xlContinuous
            .Color = RGB(100, 100, 100)
        End With
        With .Borders(xlInsideHorizontal)
            .LineStyle = xlContinuous
            .Color = RGB(100, 100, 100)
        End With
    End With
Next I
asht.Activate
End Sub

Open in new window

Comment
Watch Question

Try using .FindNext method instead of .Find

Leon

Author

Commented:
Hi Leon,

Shows error as "unable to get FindNext property" at this line.

Set c = .FindNext("*")
What if you change line 27 to

Loop While c.Address <> firstaddress

Does your original message appear at the merge cells line?
No you can not use FindNext like that. Here take a look here:

http://msdn.microsoft.com/en-us/library/aa195732(office.11).aspx

Leon

Author

Commented:
Hi StephenJR

I'm not very sure with this Q "Does your original message appear at the merge cells line?" Please elobarate more.

Author

Commented:
Hi StephenJR,

I have tested with the suggested line, the pop-up message still exist.  I have attached the workbook that I have been using for this code. We can run the macro by clicking the "Merge Cell" button. Hope you could help me to fix this.
Merge-Rows.xls
Most Valuable Expert 2012
Top Expert 2012
Commented:
There's nothing wrong with your code, you're just getting warned about a type of action Excel is taking.

Just put this command at the beginning of your code:

Application.DisplayAlerts = False

Works just fine.

See attached with code modification.

PS - if you use the command:

Application.ScreenUpdating = False

It will probably run alot faster.  Also, possibly, if you don't select cells (as opposed to just referencing them) it would go even more faster...

Cheers!

Dave
Merge-Rows-r1.xls
Most Valuable Expert 2012
Top Expert 2012

Commented:
Yep - ignore my last comment (re: selecting cells) - you aren't doing that.

However, with ScreenUpdating = False it runs in about 3 seconds...

Cheers,

Dave

Author

Commented:
Hi,

Thanks for the help.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.