Runtime Error 1004: “That command cannot be used on multiple selections.”

VBA throws Runtime Error 1004: “That command cannot be used on multiple selections” when your code attempts to perform an operation—such as copying, sorting, deleting, or formatting—on a non-contiguous range of cells that Excel cannot process as a single unit.

Quick Answer

This error occurs when executing an action on non-contiguous cells (discontinuous ranges separated by commas, like Range("A1:A5, C1:C5")). Fix it by processing each contiguous area individually using an Area loop, or by restructuring the source data into a single continuous block before applying the command.

---

Understanding Non-Contiguous Ranges

In Excel, selecting multiple distinct blocks creates an Areas collection inside a Range object. For example, Range("A1:A10, C1:C10") consists of two distinct areas:

• Area(1): A1:A10
• Area(2): C1:C10

While Excel allows formatting across multiple areas at once, it strictly prohibits specific commands across non-adjacent selections:

• Copying non-adjacent ranges of unequal sizes or shapes.
• Sorting multi-area ranges.
• Deleting rows or columns using certain batch operations.
• Pasting into multiple separate areas simultaneously.

' Triggers Error 1004:
Range("A1:A10, C1:C5").Copy Destination:=Range("E1")

---

Common Triggers and Solutions

1. Copying Filtered or Visible Rows

A very common cause occurs when copying visible cells from an AutoFilter using SpecialCells(xlCellTypeVisible). When rows are hidden, the visible cells form non-contiguous areas. If those areas have varying column arrangements or do not form a uniform rectangle, copying breaks.

' Risky on complex filtered ranges:
Worksheets("Data").Range("A1:D100").SpecialCells(xlCellTypeVisible).Copy Destination:=wsDest.Range("A1")

The Solution: Loop Through Each Area
When your range contains multiple areas, loop through the Areas collection and copy each segment one at a time:

Sub CopyVisibleAreas()
    Dim sourceRng As Range
    Dim singleArea As Range
    Dim destCell As Range

    Set sourceRng = Worksheets("Data").Range("A2:D100").SpecialCells(xlCellTypeVisible)
    Set destCell = Worksheets("Report").Range("A2")

    For Each singleArea In sourceRng.Areas
        singleArea.Copy Destination:=destCell
        ' Shift destination pointer down by the height of the copied area
        Set destCell = destCell.Offset(singleArea.Rows.Count, 0)
    Next singleArea

    Application.CutCopyMode = False
End Sub

2. Sorting Across Non-Contiguous Blocks

Excel cannot execute .Sort across a multi-area range.

• Incorrect: Range("A1:B10, D1:E10").Sort Key1:=Range("A1")
• Fix: Sort each area independently, or consolidate the data into contiguous columns before calling the sort method.

If sorting issues persist after making the range contiguous, review Runtime Error 1004: “Sort method of Range class failed” (Key parameter errors)..

3. Batch Deletions of Rows

Using SpecialCells(xlCellTypeBlanks).EntireRow.Delete often triggers this error if the dataset has many fragmented rows. Excel limits the number of distinct areas that can be passed to a delete operation at once.

The Fix: Sort the dataset first so that all target rows (e.g., blank rows) form a single contiguous block at the bottom, then delete that single block in one step:

Sub SafeDeleteBlanks()
    Dim ws As Worksheet
    Dim lastRow As Long

    Set ws = ThisWorkbook.Worksheets("Data")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    ' Sort to bring blanks together
    With ws.Sort
        .SortFields.Clear
        .SortFields.Add Key:=ws.Range("A1:A" & lastRow), Order:=xlAscending
        .SetRange ws.Range("A1:D" & lastRow)
        .Header = xlYes
        .Apply
    End With

    ' Now blanks are grouped at the bottom and can be deleted as one block
End Sub

---

Diagnostic Summary Table

Error Variant	Primary Cause	First-Line Fix
Application-defined or object-defined	Protected sheet, invalid range string, or malformed formula.	Check worksheet protection, fully qualify references, and verify formula syntax.
Copy area and paste area not same size	Destination defined as multi-cell range of wrong dimensions, or merged cells present.	Paste to a single cell (top-left) only; unmerge cells in the destination.
Command cannot be used on multiple selections	Action attempted across non-adjacent ranges (multiple Areas).	Loop through Range.Areas, or consolidate data into a single continuous block.

---

Next Diagnostic Steps

If your macro is running on a shared network drive or interacts with other open workbooks, verify that file access locks or sheet states are not contributing to the problem by reading Runtime Error 1004: “Cannot access the file ‘filename.xlsx'” (Pathing issues)..

If your code triggers a 1004 error specifically while selecting or activating sheets, see Runtime Error 1004: Why you can’t select a range on a Hidden Sheet.. To learn how to build robust error traps that record failing range addresses before the script halts, consult Building a Global Error Handler: The Err.Number and Err.Description guide..