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

Last updated on November 19, 2018 By

Summary: In the first four tutorials of this series we learned the basics of programming Excel using the VBE (Visual Basic Editor), but most of the time we relied on macro recordings of Excel’s built in functions.

Now it’s time to go deeper into the coding world and get to know the most relevant commands and macros you should use as a beginner.

Here you will find valuable commands, best-practice tips and professional ways to solve common tasks in Excel with VBA. Let’s go learn!

Difficulty: Beginner

Download the Sample Workbook

Download the sample file with VBA code

Most_Valuable_Commands (90 Kb)

#1 – Finding the last used row number

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:

Dim lrow As Long
lrow = Range("I1048576").End(xlUp).Row

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”.

lrow = Range("A1048576").End(xlUp).Row

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)

#2 – MsgBox and InputBox

We already used MsgBox and InputBox commands in our previous macro from article #4 – Editing recorded macro code.

There I mentioned how InputBox differs from MsgBox – if both are streets, then MsgBox is a one-way street, while InputBox is a two-way street.

MsgBox writes information to the screen. It can work without variables using text strings to tell the user something.

InputBox asks for information from the user – if your program needs a value, which only the user knows, then the user can give it in an InputBox. Then it will be stored and used later. That is why InputBox can’t work without variables. Without one, there is no place to store the typed in value.

In reports, or database macros, where the date or month number must always be specified, I prefer using InputBox instead of dropdown-lists in Excel. An InputBox looks more serious, and can’t be forgotten, because the macro won’t execute the next command until some value is typed in.

Sub MsgBoxAndInputBox()

Dim TodayDate As String

    Message = InputBox("Please, type in todays date!", "Date of today")

    MsgBox Message, , "Date of today"

End Sub

You can try out my macro in Module2 of the downloadable workbook.

Here are pictures of the results:



#3 – Cells.Find function to search for values

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:

Sub SearchDate()

    Cells.Find(What:="21", After:=Range("A1"), LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
End Sub
  • What:= [required] the main point of the function – this is what you’re looking for. It can be a string or a variable as well. Strings between double quotes!
  • After:= [optional] after which cell should the search start. Normally this is not a vital parameter, because it determines the start of the search and not the end of it. Search is recursive, so if it did not find the target after the specified cell, it searches before it as well. If you do not specify this argument, the search starts after the cell in the upper-left corner of the range.
  • LookIn:= [optional] you can specify if you want to search in Values only or Formulas only. If you look for a string, it doesn’t really matter, because strings are considered values AND formulas as well.
  • LookAt:= [optional] significant argument, because it determines if you want to get only full identical results, or partially similar as well.
  • SearchOrder:= [optional] search by rows or by columns
  • SearchDirection:= [optional] search forwards or backwards
  • MatchCase:= [optional] if the case of letters also matters, switch it to True
  • Searchformat:= [optional] this allows you to search for a cell format such as font type or cell color.

Range.Find to search in a range

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:

Private Sub SearchDateRange()
' Like SearchDate but for specific range

Dim RngSearch As Range

Set RngSearch = Sheet1.Range("A1:G6")

    RngSearch.Find(What:="21", After:=Range("A1"), LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate

End Sub

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

#4 – Avoiding Copy and Paste by directly assigning values

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:

Sub CopyPaste()


End Sub

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:

Sheets("Sheet2").Cells(1, 1).Value = Sheets("Sheet1").Cells(1, 1).Value

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:

Sheets(“Sheet2”).Range("A1:G6").Value = Sheets(“Sheet1”).Range("A1:G6").Value

Note on using Sheet Index numbers

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

Sheets(3).Range("A1:G6").Value = Sheets(2).Range("A1:G6").Value

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.

#5 – Disabling Screen Updating to speed up macros

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:

Application.ScreenUpdating = False


Application.ScreenUpdating 0

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:

Application.ScreenUpdating = True


Application.ScreenUpdating 1

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.

#6 – Summary

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

  • Finding the last used row number to work with datasets that vary in size
  • Giving messages to the user with MsgBox, and getting inputs from the user with InputBox
  • Searching for values with the Cells.Find function
  • Avoiding Copy and Paste by directly assigning values
  • Making your code run faster by disabling screen updating

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!

#7 – About the Author

Daniel Lajosbanyai – I work as a controller and accountant for a company with a bunch of international subsidiaries. In my daily job I work A LOT with Excel and my tasks are quite repetitive (and sometimes boring!)

To boost my processes and spare some time and energy, I started learning Excel Macros. My first teacher was a university professor, who showed me how to get started. I am really thankful to him, because without this knowledge I might have quit my job years ago.

Now I enjoy writing macros for every task I can automate and feel really happy to have learned this skill! Why should we do repetitive things, when our computers can do them quicker for us? We only need to learn how to give them instructions to follow!

Related Posts:

Connect on YouTube, LinkedIn, Twitter.

Hey, I'm Victor Chan

Are you struggling with complex Excel tasks? Feeling overwhelmed by spreadsheets that are hard to use?

Many people believe mastering Excel is about learning shortcuts, functions, and formulas. But this overlooks the importance of building practical, real-world applications. It's not just about knowing the tools. It's about using them effectively.

That's where I come in. You'll get a unique perspective to Excel training from me. I have over 20 years of experience at Deloitte and two global tech companies. And I know what can make a difference in your career.

Let me help you integrate Excel into your professional life. Starting today. Read one of my articles, watch one of my videos. Then apply the new technique to your work. You'll see the difference immediately!

Recommended Posts

Discover the PROVEN Blueprint for transforming your Excel skills, supercharging your productivity, and standing out in your career! My course helps you to learn Excel VBA and save hours of time even if you have zero prior experience with programming.

Solve tricky Excel problems and take your work to the next level! Get customized solutions for your unique needs. Save time and gain insights with truly expert Excel solutions from only $97 per task.

Get a clear overview of your project progress using the Excel project timeline. Use it to communicate the big picture, track task progress, and stay on top of your project goals. Stay organized with our project timeline!

Our cheat sheets provide quick and easy reference to commonly used Excel VBA concepts and code snippets.

Unlock new levels of productivity and efficiency with our cheat sheets, and write VBA code like a pro in no time.


Are you looking to upskill and stay ahead of the curve? Excel is a powerful tool that keeps growing in demand. We round up the best online courses for learning Excel.

Are you looking to up your spreadsheet game? Excel is an invaluable tool that can help you stay organized and save time. From data analysis to budgets, Excel can do it all!

Today, having Excel skills is more critical than ever. Those who know how to use Excel are more likely to find higher-paying jobs. And get promoted faster.


Step up your Excel game! Join our free email newsletter and get updates on how to become more awesome at Excel.