Macro to ignore merge cell message

Hello All,

I have a macro that I need to clear data first from Spreadsheet and this means unmerge cells as well as later merge them. I used the following code for the:

Unmerging:

Dim lrowVis As Long
Application.ScreenUpdating = False
With Sheets("Visualisation")
    lrowVis = .Cells(Rows.Count, "Z").End(xlUp).Row
    .Range("Q2:Q" & lrowVis).Select
        With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = True
    End With
    Selection.UnMerge

and Merging:

Range("V2:V9").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 60
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.Merge

with a copy down:

    Range("V2:V9").Select
    Selection.Copy
    Range("V10:V" & lastr).Select
    Selection.Paste
    ActiveSheet.Calculate
    Application.CutCopyMode = False

My problems is when I run the Macro it comes back with the pop out advising me of losing data that is merged as only the top row cell info is going to be displayed.

how can I get rid of that message - the macro answering yes to itself and carrying on?

Appreciate suggestions.

Thank you in advance for trying out.

Simon

Shall I remove the With => End With part when unmerging?

Have you tried wrapping

Application.DisplayAlerts = True
.....code...
Application.DisplayAlerts = False

around the line of code which fails

Best suggestion is to remove all merged cells permanently - they cause too many VBA problems!

Hi kev,

Thank you for your suggestion.

I need to have merged cells unfortunately as this is all about the display later.

Regarding the wrapping around the code do you reckon I shall do it before and after the With section?

Or before Range and after unmerge instruction?

You can wrap the code anywhere around the offending line - but it is safer always to wrap only lines triggering messages you will always want ignored.
What the code does is to suppress most warning pop-ups until the value is set to true.

You can safely wrap it around all lines involved in merging or unmerging - but I would not wrap code doing anything else.

To avoid using merged cells - locate text boxes above the cells and pull values into text boxes from the cells underneath
- when making things pretty gets in the way of functionality I leave things ugly every time

Thank you kev.

I have resolved it.

I have changed the range from 1 column at the time to all needed to unmerge and wrapped it around with that code regarding alerts. Then reapplied the merging for each at a time and instead of copying and pasting I have done each column as merge + copy and paste Formats + paste formulas and it worked.

Thank you for your help.

Appreciate it.

glad you found your solution, thanks for the reps