How to: Excel VBA clear clipboard – STL

In Excel there’s a lot of data being moved around, perhaps via copy and paste. If you leave a lot of stuff lying around on in memory or cache you’ll get a message along the lines of “you’ve left a lot of information on the clipboard…”. Generally in VBA you can avoid this with other methods but sometimes copying and pasting is the only way. How do you clear the clipboard in Excel VBA?

It’s done with a simple line of code which you place after the paste operation:

Application.CutCopyMode = False

You might see this in macro recorded code – it is created when you press ‘Esc’ on the keyboard to stop copying, or if you’ve simply pasted. This line of code empties the clipboard and clears the memory cache.

Smarter version

You can avoid these altogether by bypassing the clipboard entirely. This is one of those things you can only do with VBA. There’s a silent Destination argument of the Copy command, which isn’t obvious unless you press a space immediately after a Copy instruction. You certainly won’t get it from the macro recorder.

Here’s a chunk of code I just recorded, copying and pasting a block from one sheet to another:

    Range("A1:J4292").Select
    Range("F3221").Activate
    Selection.Copy
    Sheets("Sheet2").Select
    Range("A1").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False

Lots of typically superfluous code there. Lines that end “…Activate” are almost always useless, indicating which cell has been selected – why this is useful in this context I don’t know. Here’s a truncated version:

Range("A1:J4292").Copy Sheets("Sheet2").Range("A1")

With this command it doesn’t use the clipboard so no Excel VBA clear clipboard command required. And of course, we have one line of code instead of seven. Generally speaking less code means quicker execution, but there’s lots of exceptions to that.

If you want to explore this further, type up the .Copy bit then press space. Examine the arguments for further insight into how you can use this command.

Bonus marks – work with any range of data, from anywhere in the workbook

By prefixing the command with the source sheet (“Sheet1” in this case), and adding CurrentRegion off the starting cell will continue selecting until it has captured all of the data (it will stop at completely empty rows and completely empty columns):

Sheets("Sheet1").Range("A1").CurrentRegion.Copy Sheets("Sheet2").Range("A1")

Smarter version

Bonus marks – work with any range of data, from anywhere in the workbook

By Richard Bailey