This video will outline how to write a VBA dynamic ranges macro. It will enable users to seamlessly select a changing data range. We will compose a macro, which will dynamically locate the last row and last column of a data set.

Here is a screenshot of our sample worksheet. We will use in it in this tutorial to demonstrate the customized macro.

Here is the underlying data set, which the following video uses to outline the macro.

Composing the VBA dynamic ranges

To write a macro in Excel, go to Visual Basic for Applications. Then insert a module. We will call the macro dynamicRange

It is advisable to disable certain Excel properties, whilst we run our customized macro to locate dynamic ranges. As this will improve the overall performance of the macro, whilst it dynamically selects the range. This piece of the code is located below.

How to disable Excel properties
Disabling these Excel properties improves a macro’s performance.

The macro must define some key variables. The startCell variable must direct the macro to the first cell in the range. The lastRow variable will identify the last row in the data range. Whilst the lastCol variable will state the last column of the range in the below video.

Finding the last row and last column for the VBA dynamic ranges
The above code is needed to identify the last row and last column in the range.

Testing the macro

Once we have reviewed the macro, and we are happy with the code and layout. We can road-test the macro on our data. Close out of Visual Basic for Applications. Then insert a form control button. Allocate the dynamicRange macro to this button.

If the macro is working, you will see the cursor dynamically select the following data range.

In summary, here is the example workbook, which the above video showcased how to compose the macro.

Download SpreadsheetVault – Dynamic Range (solution).xlsb