Excel VBA: Clear Selection After Copying Cells

Sharing is caring!

Working with Excel VBA gives users the flexibility to automate tasks, improve productivity, and reduce repetitive work. One common task is copying data from one place to another. However, after a copy operation, Excel often leaves the copied range selected and in “marching ants” mode. This can be distracting or confusing for users.

In this article, you’ll learn how to clear selection after a copy using VBA, along with practical examples, useful syntax, and best practices.

Why You Should Clear Selection After Copy in VBA

When you use VBA to copy cells, Excel highlights the copied range with a dotted line. This is called the copy selection marquee. It stays active until another action interrupts it, like pressing Escape or pasting the data.

Here’s why it’s useful to clear the selection:

• Improves user experience by removing unnecessary highlights.
• Prevents accidental pasting in the wrong cell.
• Makes your macros look cleaner and more professional.
• Reduces confusion when automating complex reports or dashboards.

Basic VBA Copy Code Example

Let’s start with a simple VBA macro that copies a range of cells:

Sub CopyData()
    Range("A1:B5").Copy
    Range("D1").PasteSpecial Paste:=xlPasteValues
End Sub

After running this code, cells A1:B5 are copied, and values are pasted to D1. But the source range remains selected with the dotted line, and the clipboard is still active.

To clear the selection, we need to empty the clipboard or cancel the cut/copy mode.

How to Clear Copy Selection in VBA

Method 1: Use Application.CutCopyMode = False

The most reliable way to remove the copy selection is to set CutCopyMode to False.

Here’s how to apply it:

Sub CopyAndClear()
    Range("A1:B5").Copy
    Range("D1").PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
End Sub

Explanation:

• Application.CutCopyMode = False tells Excel to turn off the copy mode.
• This removes the dotted outline and clears the clipboard.
• It’s the most common method used in VBA scripts.

Method 2: Use SendKeys "{ESC}"

Some developers try to simulate pressing the Escape key to cancel the selection:

Sub CopyUsingSendKeys()
    Range("A1:B5").Copy
    Range("D1").PasteSpecial Paste:=xlPasteValues
    Application.SendKeys "{ESC}"
End Sub

However, this approach is unreliable and not recommended because:

• It depends on keyboard input timing.
• May not work consistently across different versions of Excel.
• Can interfere with user actions or other macros.

Always prefer Application.CutCopyMode = False over SendKeys.

Best Practices When Clearing Copy Selection

Here are some tips and best practices when you use VBA to copy data and clear selection afterward:

Practical Example: Copy Data Between Sheets and Clear Selection

Here’s a more practical VBA macro that copies data from Sheet1 to Sheet2, pastes only the values, and clears the selection:

Sub CopyBetweenSheets()
    Dim wsSource As Worksheet
    Dim wsTarget As Worksheet
    
    Set wsSource = ThisWorkbook.Sheets("Sheet1")
    Set wsTarget = ThisWorkbook.Sheets("Sheet2")
    
    wsSource.Range("A1:B10").Copy
    wsTarget.Range("A1").PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
End Sub

What this macro does:

• Selects data range from Sheet1.
• Pastes the values into Sheet2.
• Clears the selection using CutCopyMode.

Alternative: Use Value Assignment Instead of Copy

If you’re copying only values and not formatting or formulas, you can avoid the clipboard completely. Assigning values directly is faster and cleaner.

Sub CopyWithoutClipboard()
    Range("D1:D5").Value = Range("A1:A5").Value
End Sub

Benefits:

• No clipboard involved.
• No need to clear selection.
• Faster and lightweight macro.

However, this method only works if you’re copying values only, not formats or formulas.

Clearing Multiple Copy Selections

Sometimes you might have multiple ranges copied in different parts of a script. In such cases, it’s good to clear the clipboard after each operation.

Sub MultipleCopyOperations()
    Range("A1:A3").Copy
    Range("D1").PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False

    Range("B1:B3").Copy
    Range("E1").PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
End Sub

This ensures there’s no leftover copy selection from earlier steps.

Use a Function to Handle Copy and Clear

To keep your code modular and clean, you can create a custom VBA function that copies data and clears the selection automatically:

Sub CopyAndPasteValues(sourceRange As Range, targetRange As Range)
    sourceRange.Copy
    targetRange.PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
End Sub

Sub UseFunctionExample()
    Call CopyAndPasteValues(Range("A1:A5"), Range("C1"))
End Sub

Advantages:

• Reusable in multiple macros.
• Automatically handles the clipboard.
• Cleaner and maintainable code.

Common Mistakes to Avoid

Avoid these common errors when trying to clear copy selection:

Comparison Overview

Final Thoughts

Using VBA to copy data is one of the most frequent automation tasks in Excel. But leaving the copy selection active can make your macro feel unfinished. By adding Application.CutCopyMode = False, you ensure the copy selection is cleared, providing a smoother user experience.

When possible, prefer direct value assignments to avoid the clipboard altogether. For advanced tasks, wrap your copy-and-clear logic into custom functions for better readability and reuse.

Vaishvi Desai

Vaishvi Desai is the founder of Excelsamurai and a passionate Excel enthusiast with years of experience in data analysis and spreadsheet management. With a mission to help others harness the power of Excel, Vaishvi shares her expertise through concise, easy-to-follow tutorials on shortcuts, formulas, Pivot Tables, and VBA.