Fix 'This action won't work on multiple selections' in Excel Non-Contiguous Ranges

The Situation

You Ctrl+clicked across several disconnected ranges — say, columns A, C, and E in a quarterly report — then tried to merge cells, sort, or apply a format. Excel immediately throws:

This action won't work on multiple selections.

The highlight vanishes. Nothing applied. This is one of those Excel limitations that ambushes you halfway through cleaning up a report.

Why Excel Blocks This

The root cause is geometry. Excel draws a hard line between contiguous ranges (one solid rectangle, like A1:C10) and non-contiguous selections (disconnected areas held together with Ctrl). Operations like Merge or Sort need a single, unambiguous rectangle to work with. When you select columns A and C separately, Excel can't determine where a merged cell goes — or where a sort table begins and ends.

Operations that trigger this error on non-contiguous selections:

• Merge & Center — needs one rectangle
• Sort — requires a single contiguous data range
• Insert/Delete rows or columns — when selected areas span different rows or columns
• Paste Special → Transpose
• Group rows/columns (Data → Group)
• Create Table (Ctrl+T)

What does work across non-contiguous selections: basic formatting (bold, color fill, font size, number format), Clear Contents, and deleting cell contents with the Delete key.

Quick Fix: Work One Range at a Time

The fastest workaround — click the first range, do the operation, then move to the next. Tedious if you have 8 separate columns, but zero risk of data corruption.

For merge specifically, try Center Across Selection instead. It looks identical to a merged cell but doesn't actually merge anything — so it sidesteps the error entirely.

• Select the cells you want to center across
• Ctrl+1 → Alignment tab
• Horizontal: Center Across Selection
• OK

Individual cell identity stays intact, which means formulas, sorting, and filtering all work normally.

VBA Fix: Apply an Operation Across All Areas

Doing this repeatedly? A short macro that loops through Selection.Areas handles it cleanly. Each Area is a separate contiguous sub-range within your non-contiguous selection — Excel's internal way of representing the individual pieces.

Example: Merge Each Non-Contiguous Block Separately

Sub MergeEachArea()
    Dim area As Range
    For Each area In Selection.Areas
        With area
            .Merge
            .HorizontalAlignment = xlCenter
        End With
    Next area
End Sub

Example: Sort Each Area Independently

Sub SortEachArea()
    Dim area As Range
    For Each area In Selection.Areas
        area.Sort Key1:=area.Cells(1, 1), Order1:=xlAscending, Header:=xlYes
    Next area
End Sub

Example: Apply Number Format to All Areas

Sub FormatAllAreas()
    Dim area As Range
    For Each area In Selection.Areas
        area.NumberFormat = "#,##0.00"
    Next area
End Sub

To run: Alt+F11 → Insert Module → paste the code → F5, or assign it to a button on the ribbon.

Paste Special on Non-Contiguous Targets

Paste errors here usually mean the destination is non-contiguous, not just the source. When the target has gaps, Excel can't map each copied cell to a clear landing spot.

Two options: paste into the first target range, then hit Ctrl+Y (Repeat) for each subsequent range. Or paste into a temporary contiguous block first, then cut-paste to your actual destinations one at a time.

Format Painter Workaround

Format Painter handles multiple selections just fine. Double-click the brush to lock it, then click each target range in turn. Press Escape when done. For pure formatting jobs, this sidesteps the error with no code required.

Permanent Workflow Adjustment

Run into this every week? Store the VBA macro in your Personal Macro Workbook (PERSONAL.XLSB) so it's available across all files, not just the current one. Then wire it to a Quick Access Toolbar button.

• Open PERSONAL.XLSB (Alt+F11 → VBAProject (PERSONAL.XLSB) → Insert Module) and paste the Area loop macro
• File → Options → Quick Access Toolbar → Choose commands from: Macros
• Add your macro and assign a display name and icon

Next time: Ctrl+click your non-contiguous ranges, hit the toolbar button once. Done.

Verification

• After running the VBA loop, click into each area and confirm the operation applied — merged, sorted, or formatted as expected
• For merge: click a cell in the merged area → the Name Box (top-left) should show a range like A1:C1, not just A1
• For sort: verify the header row is still in position and data rows are in ascending order
• No error dialog means success. Add error handling to catch silent failures:

Sub MergeEachAreaSafe()
    Dim area As Range
    On Error Resume Next
    For Each area In Selection.Areas
        area.Merge
        If Err.Number <> 0 Then
            MsgBox "Merge failed on " & area.Address & ": " & Err.Description
            Err.Clear
        End If
    Next area
End Sub