Goal: Create a macro to select dynamically changing rows ranges of Excel spreadsheet data.

This video will demonstrate how to write a VBA macro, which will dynamically select a data range based on changing rows of data.

This is a major drawback of a macro recorder, because they can’t facilitate adjusting row ranges in a data set. Let’s begin the creation of the VBA macro, which will enable dynamic row selection.

Technical explanation

Using the keyboard shortcut Alt F11, or right-clicking on the mouse and choosing “view code”; we can open up VBA Editor. Select Insert Module, because this is the better place to store macros – instead of inside the workbook or worksheet objects.

It’s good to get into the habit of giving your macro a short header, with a title and description of the macro, which will explain to users the purpose of the macro. A VBA procedure unit or block, which is the technical term for a macro, comprises of a procedure statement (Sub or Function), and an ending statement with statements (or code).

Firstly to optimize the speed and efficiency of the macro, it is preferable to disable the following Excel object applications. Turning the Calculation application to Manual will prevent the formulas, in the underlying spreadsheet from recalculating whilst the macro runs.

If you place Private, before the procedure type, this will render the macro private to users in Excel. Subsequently, users will be unable to run the macro, if they select on View Macros in the spreadsheet.

Writing the dynamic row ranges macro

Hence, make sure to give the macro a concise, yet explanatory name to users – this macro is called dynamicRowRange.

Disabling the Enable Events object will prevent Excel from calling event procedures, such as Workbook or Worksheet events, during the execution of the code. Disabling the ScreenUpdating feature will prevent Excel from following the tasks assigned to the macro.Like the previous two objects, it will ensure a more rapid completion of the macro.

The Visual Basic compiler uses the Dim statement, short for Dimension. Dimension will ascertain the variable’s data type and other information. Such as what code can read the variable. Although it is not mandatory to declare all variables. Given any undeclared variables are stored as a variant. This causes Excel to use more memory.

Further, by referencing elements of an Excel spreadsheet, i.e. Range(“B4”) instead of via a declared variable. It will result in the macro constantly interacting back and forth with Excel. By applying Set in a macro, it will greatly reduce the amount of code, not to mention improve the speed of the macro.

Here we will set the ws variable to Sheet1, and the startCell variable to Range(“B4”). Finding the last row of the data set, in a dynamic fashion, will dictate the macro’s overall value. Using the lastRow variable, along with the ws variable, will enable the macro to count the number of rows – courtesy of the End(xlUp) syntax.

The End syntax means the macro locates the last cell in that range. Whilst xlUp goes up the column until it finds something that is not blank. The macro is now ready to dynamically select the last row of data, based on the startCell variable, followed by the lastRow variable and column G. For this case study, we assume column G is always the last column for the purposes of this dynamic row ranges macro.

The macro is now complete, and so we can re-enable the earlier Excel features, which we earlier disabled during the execution of the macro.

Testing the macro

Let’s save and close out of VBA Editor. We can run the macro to ensure it is referencing dynamically changing rows of data. We will insert a Form Control button, and assign the macro to this button.

Great the macro is selecting the data array.

Let’s add some new rows of trade data, as in the following & check the macro is still working. Finally, we’ll clear data from the range & witness the macro’s full value.

In summary, here is the source workbook featured in the video. It contains the macro, which allows Excel users to compose a dynamic row ranges macro.

Download SpreadsheetVault – Dynamic Row Range (solution).xlsb