[Excel VBA] Macro to Paste All Except Borders

2025年12月26日

Introduction

Have you ever ruined the design of a carefully formatted report by pasting cells with thick borders from another location?

Using “Paste Special” and selecting “All except borders” is one solution, but it is tedious to do this frequently. By creating a VBA macro, you can execute this “Paste All Except Borders” action with a single click, eliminating this small stress from your work.

Sample VBA Code

The following macro pastes the content currently in the clipboard to the active cell (the currently selected cell) using the “All except borders” option.

Complete Code

' Pastes clipboard content to the active cell, excluding borders
Sub PasteAllExceptBorders()

    ' Check if a cell range is selected for pasting
    If TypeName(Selection) <> "Range" Then
        MsgBox "Please select a cell range to paste into.", vbExclamation
        Exit Sub
    End If

    ' Execute paste with the "All except borders" option
    Selection.PasteSpecial Paste:=xlPasteAllExceptBorders
    
    ' Clear the copy mode (remove the marching ants border)
    Application.CutCopyMode = False

End Sub

How to Use

Select the cell range you want to copy and copy it normally (Ctrl + C).
Select the destination cell where you want to paste.
Open the VBE (Alt + F11), paste the code above into a standard module, and run the PasteAllExceptBorders macro.

The values, formulas, and formats will be pasted, but the borders will be excluded.

Code Explanation

`Selection.PasteSpecial Paste:=xlPasteAllExceptBorders`

This single line is the core of the process.

Selection: Refers to the currently selected object (in this case, the cell range).
.PasteSpecial: The method used to perform “Paste Special.”
Paste:=xlPasteAllExceptBorders: This argument specifies the format to paste. xlPasteAllExceptBorders is a built-in VBA constant that means “All information except borders.”

`Application.CutCopyMode = False`

This command clears the “Copy Mode,” removing the dotted line (marching ants) around the original copied cells. This effectively does the same thing as pressing the Esc key.

Summary

In this article, I introduced a way to smartly perform “Paste All Except Borders” using VBA.

If you register this macro to your Quick Access Toolbar, you can use it as your own personal “Paste Without Borders” button at any time. If you frequently use copy and paste in your daily documentation work, please try this macro to experience improved efficiency.

よかったらシェアしてね！