Category: VBA Training

Posted on

How to: Excel VBA clear clipboard

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?

excel vba clear clipboard

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")
Posted on

Excel VBA Uppercase, Lowercase and more

Ever received a spreadsheet and all the data on it is in the wrong case? You might received a dodgy dump of data from your website or database, or maybe the user-inputted data is poorly or inconsistently formatted. Here I’ll guide you through how to do Excel VBA Uppercase, lowercase and capitalising the first letter of a string. All these tasks are invaluable when you have to manipulate your data using VBA.

wrong kind of case. we want excel vba uppercase
Not this kind of case! (image copyright iconarchive)

Excel VBA Uppercase

First, converting strings to upper case through Excel VBA: you need the Ucase function. Let’s say you want to loop through column A, replacing the current cell with an upper case version:

For i = 1 to cells(Rows.Count, 1)

     Cells(i,1) = Ucase(Cells(i,1))

Next i

And that’s how you do Excel VBA Uppercase.

Excel VBA Lowercase

Lower case couldn’t be simpler: the Lcase function. Here’s an example of converting a variable to lower case:

Dim empStatus As String

empStatus = "CONTRACT"

empStatus = Lcase(empStatus)

MsgBox empStatus

How do I capitalise the first letter in a cell (or string) ?

Excel VBA does not have a native way to do this; you could use some clumsy string manipulation methods but let’s lean on the Excel functionality: the Excel Proper function. You can summon any function from Excel using the Application.WorksheetFunction method.

Here’s an example, capitalising the first letter of every cell in column C:

For i = 1 to cells(Rows.Count, 3)

     Cells(i,3) = Application.WorksheetFunction.Proper(Cells(i,3))

Next i

Three quick ways to manipulate text strings using Excel VBA,