Category: Excel Training

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.

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,

Excel VBA is used for all sorts (!) of data manipulation tasks. One of the most common is sorting your data. Performing an Excel VBA sort seems trivial but some parts of it may misbehave if you’re not diligent.

I’ve seen some people try to write their own sort routine in Excel VBA, but frankly Microsoft has spent thousands (millions?) of dollars refining their Excel Sort technique, so why not just record a macro where you use that?

It starts simply enough. View > Macros > Record new macro, fill in the details, hit Sort then Stop Recording.

Play it back – seems fine.

Add some new rows, play it again – still fine.

But it isn’t.

The problem comes when you look at what has been sorted. If you notice, your macro has only worked on the same range as your original recorded macro. Extra data outside of that first selection will not be included.

A quick peek in the Visual Basic Editor (ALT + F11) will demonstrate this clearly.

Boom, there it is. A reference to a fixed range of cells (A2:H31). Aside from the other typically overcautious lines from the macro recorder (thank goodness the xlPinYin method was set!) these cell references are the bits that will cause you problems. It’s the worst kind of error too, the one that doesn’t cause an error but silently causes havoc in the background.

The key is then to create a dynamic range. This is the source of many issues with beginner code in VBA. As with most VBA problems, there are many ways to solve it. Try this out:

Sub MySortMacro()

    Dim LastRow As Long
    LastRow = Cells(Rows.Count, 1).End(xlUp).Row
    Range("A2:H" & LastRow).Sort Key1:=Range("C3:C" & LastRow), _
       Order1:=xlAscending, Header:=xlNo

End Sub

Let’s take a look at that.

    Dim LastRow As Long

Create a new variable called LastRow as a Long datatype (an integer that goes roughly up to 2 billion). Why a Long and not an Integer? An Integer goes up to ~36,000. If your data is 36,000+ rows, you will run into the overflow error. 2 billion is plenty!

    LastRow = Cells(Rows.Count, 1).End(xlUp).Row

Count how many rows are in the first column (,1). Go to that last cell and do a CTRL + up arrow motion. In most sets of data this will hit the last item of data in that column. Get the row number then store that in the LastRow variable.

    Range("A2:H" & LastRow).Sort Key1:=Range("C3:C" & LastRow), _
       Order1:=xlAscending, Header:=xlNo

This heavily truncated command concatenates (or ‘joins’) the LastRow variable to the ranges required. The “A2:H” bit is the full range of data, “C3:C” is which column you want to sort by.

There you go. Simpler code, easier to read and will work with any range of data.