Excel VBA: 6 Ways to Find the Last Row of a List

A very common task in Excel VBA is to find the last row of a list. This can be for many reasons such as to make a chart or PivotTable ranges dynamic, or to append fresh data to the bottom of a list.

This blog post uncovers 6 different ways you can find the last row. In these examples the number of the last row is assigned to a variable for use within your procedures.

Are you new to Excel VBA? Learn Excel VBA quickly with this online course.

Find the last row using Excel VBA

6 Ways to Find the Last Row of a List – Excel VBA

For these examples the following variables have been declared and the sht variable activated.

Dim sht As Worksheet
Dim LastRow As Long

Set sht = ThisWorkbook.Worksheets("Sheet1")

Continue reading

Count Cells by Colour – Excel VBA Function

If you have ever tried to count cells by colour in Excel, you may have noticed that Excel does not contain a function to accomplish this.

We can find out how many cells by filtering the list by colour, and maybe use the AGGREGATE function to return the count. This is an option. But having to filter the list each time may not be good enough.

Because functions such as COUNTIF cannot count by cell colour, we will need to create our own custom function (also known as User Defined Functions or UDF’s) to get the job done. Continue reading

Export Multiple Sheets to a Single PDF File – Excel VBA

This tutorial will demonstrate how to export multiple sheets of a workbook into a single PDF file using Excel VBA. The code can be seen below and adapted for your own use.

I have previously written a tutorial on exporting all the sheets of a workbook to PDF using VBA. This macro will allow you to be more specific as to what sheets are exported. It will also export to a single PDF file, rather than a separate file for each sheet. Continue reading

Loop Through Subfolders using FileSystemObject

A common Excel VBA requirement is to work with different files and folders of a directory. It may be that you need to search and open a file, list the names of files from a folder, print all the files from a folder, or even delete files.

The different tasks you may need to perform are endless, but fortunately Excel VBA provides an easy way to access and perform operations on the files and folders of a directory. This easy way is known as FileSystemObject.

To use FileSystemObject, otherwise known as FSO, you first need to create an instance of FileSystemObject. You can then access some of the objects from its model such as File and Folder to perform different tasks. Continue reading

Email Workbook as an Attachment – Excel VBA Macro

The functionality in Excel to send a workbook via email as an attachment is fantastic. However if you find yourself using this feature often, you may have a desire to automate or speed up the process.

I have been asked this many times in my Excel VBA classes so thought it would be beneficial to share some code to accomplish this.

This Excel VBA Macro will attach the current workbook as an attachment to an email and send it. It will add some text to the subject line and body of the email also.

This code can be adapted to your own needs. I will explain how to do this and the lines you will need to change. There are also a few things to look out for that may cause you problems.

This macro should work for any email service. I have used it with my GMail and my Yahoo accounts successfully. The code may just need some tweaking to work for your own situation. Continue reading

Pick Names at Random from a List – Excel VBA

In this blog post we will explore how to pick names at random from a list using Excel VBA.

Suppose we were picking the names for a draw or competition and needed to generate a list of maybe 3, 5 or any number of names from a list.

These names must be selected at random and be unique. You cannot select the same name more than once.

The macro code shown below can be copied and pasted into a VBA module and adapted for your use. Comments have been used to identify the key and more complex parts of the macro.

This macro uses an array variable to store the names being randomly selected from the list. Every time a name is selected, it is checked against this array to ensure that it has not already been selected. If it has, then another name is randomly selected. Continue reading

Export All Sheets to PDF – Excel VBA

This tutorial looks at how you can create a macro in Excel to export all sheets to PDF. Each sheet of a workbook will be saved into the same folder. The name of the worksheet will be used as the filename of the PDF.

By creating a macro this seemingly repetitive task can be performed at the click of a button. Continue reading

Add a Hyperlink to a Userform – Excel VBA

If you have ever tried to add a hyperlink to a userform, you would have noticed that there is no hyperlink button on the toolbox.

You may not be able to insert a hyperlink like you would on an Excel spreadsheet, but you can create a link by inserting a label and applying the OnClick event.

Adding a Hyperlink to a Userform

In this example we will add a hyperlink so users may email an enquiries department from the userform. Continue reading

Format Specific Words in a Cell using VBA

In this blog post we look at how to create a macro that will look in a list and format all the instances of specific words that appear in a cell.

This macro is case sensitive and will also only apply the formatting if it is the whole word. For example, if you are formatting all instances of the word ‘red’, this macro will not format it if used in words like ‘reddish’ and ‘sacred’.

Continue reading

Add Your Own Function to the Status Bar in Excel

When a range of cells are selected, Excel automatically displays formula results on the status bar. This can be an incredibly useful feature of Excel, especially when combined with filtering data.

Excel can display the sum, average, max, min, count of cells containing values and count of cells that are not blank in the status bar.

Excel displaying formulas in status bar

This blog post looks at how you can get Excel to display your own function on the status bar. For example, you may need to know the median or mode value, or count the number of blanks in the selected range.

In this post we will get Excel to display the number of blank cells in the selected range on the status bar.

Continue reading