Excel VBA Tutorial – #5 Most commonly used Excel VBA commands

Working with big datasets in Excel requires a lot of scrolling with the mouse. You need to make sure you select the last row of data and not miss any valuable information.

When you write VBA macros, it’s similarly important to define the last row of a dataset. Macro execution is just too fast to check if everything goes well, and the right range is selected. So don’t just rely on hard coded ranges.

This is where finding the last row number comes in handy: we use a variable to find and store the number of the last used row on the sheet.

This is dynamic, so if you use it on another sheet, it will store that sheet’s last row number.

The code to do this is really short:

The first line is the variable declaration, which you should write at the start of the macro. Here we define “lrow” as a variable with Long type.

What does Long type mean? It can store whole numbers from -1,048,576 to +1,048,576.

The second line is the function that finds the last used row in column “A”. You can of course select any column, just change the letter “A” in the command.

The interesting thing is that you can also record this kind of function in Excel: start recording a macro, then select the last unused cell on a sheet, and press Ctrl+UpArrow, then stop recording the macro.

In Excel 2016 version every sheet has 1,048,576 rows and this is the maximum you can use. So if you select Cell(A1048576) and go up with the keyboard shortcut until the last used cell, you will get the following recorded VBA code:

We combined these two lines into one line, using the “.Row” property of the last used cell. And the result will be stored in our variable called “lrow”.

This is a very effective and useful command, and that’s why it’s my first topic for this article. I can hardly imagine more complex macros where something like this is not used.

In the downloadable .xlsm workbook you will find Sheet1 has lots of “a” characters typed in column I. And you can test out the last used row finder function by running the Sub LastRowFinder() in Module2. Have fun!

(Note: for more advanced ways to find the last used row, read this in-depth article… Excel VBA Tutorial: Find the Last Cell, Row or Column on an Excel worksheet)

The search function in Excel is also one of the most often used tools in the VBA programmers’ toolbox. If you do not know exactly where the needed cell is in the dataset, you can always turn to Cells.Find.

In the previous article we looked at how to use the search function to locate the date among cells containing the November 2018 calendar…

… but we didn’t go deep with the arguments, which are the different input values you can use to customize the behavior of the function.

Here I’ll go through the different options for finding a value.

This example searches all the cells of the active worksheet to find the number 21:

You don’t have to search the whole sheet, you can work in a smaller range.

In this case you should determine a Range type variable, then specify the area of the range.

After this you can use the RangeName.Find() function, where the RangeName is the name of your Range type variable.

Here is an example:

This gives you faster results because of the smaller area to search through.

Copy-Pasting values between sheets and workbooks – everyone does this. I’m sure it’s one of Excels most used functions.

Why do I say then that you should avoid it in macros? Because it can slow down your code, and you can frequently achieve the same result in a better way.

In the downloadable workbook, Module2 contains a CopyPaste macro, which is 6 lines long.

Here’s the code which copies cell A1 on Sheet1 and pastes to cell A1 on Sheet2:

Note that I only activated one specific cell, but the collection is still in plural form “Cells” – because it is a collection, and I need only one item from it.

You can of course select a group of cells as well:

You might say that 6 lines of code isn’t much… why should we use another way?

Because later on, you will be copying massive datasets full of complicated Excel formulas, and you may have many sheets and workbooks to work with, so every single simplification in the code can save you minutes or even hours!

As you can see in the CopyPaste macro, copy-pasting requires cell selection.

This is one of its biggest flaws… When the program selects a cell, it is a waste of processor time.

Instead of the selection command, the processor could do something more valuable, which in fact does something to your data, and not just selects a cell.

In our case it’s better to assign values directly.

To copy of value in cell A1 of Sheet1 to cell A1 of Sheet2, we can use one line of code:

This is the professional 😊 way of copying values from sheet to sheet.

It also works for Ranges, so we can do this to copy range A1:G6 of Sheet1 to range A1:G6 of Sheet2:

I could also write the above line of code this way:

Note in this line of code that I didn’t write the sheet names inside the brackets, just their index numbers.

This is a simpler way of coding, but it has risks as well.

In particular if I reorder the sheets, the macro runs on the wrong sheets!

So, if you don’t specify the sheets by their names, be careful, and always make sure that the order of the sheets is not changed.

To make your code run smoother and a lot faster, you should disable the screen updating.

With this option you lose the ability to follow what the macro is doing on screen – but with today’s quad-core (and more) processors you would barely catch anything happening anyway.

The command is as follows:

Or:

The second option is shorter. If you code a lot, after a certain time you will seek the opportunity to use shorter codes, to spare typing time.

You can of course enable the screen updating after it has been disabled. The command is the same, just the value is opposite:

Or

You should always re-enable screen updating at the end of the macro, so it won’t affect other macros. This “switch” holds its effect, until the opposite command is executed – you should always use the command in pairs. After disabling, always enable when you are finished.

We covered 5 best practice code snippets that I use in my daily work:

I’m sure you can make good use of them as well, because they are very common functions that you can apply on many tasks in Excel!

Learning and using these methods means you’re no longer an Excel VBA beginner – these are intermediate level commands.

They are shorter, more effective and more elegant solutions for frequent tasks. So, I advise you to use them for your own good, and don’t be afraid to modify them to suit your needs!