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 macro recordered macros,
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.
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.
In this case it will be a Sub Procedure.
If you place Private, before the Procedure type,
this will render the macro private to users in Excel.
They will be unable to run the macro, if they select on "View Macros" in the spreadsheet.
Make sure to give the macro a concise,
yet explanatory name to users.
This macro is called dynamicRowRange.
Firstly to optimise 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.
Disabling the EnableEvents 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.
The Visual Basic compiler uses the Dim statement,
short for Dimension, to 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;
any undeclared variables are stored as a variant,
which 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.
End means the macro locates the last cell in that range,
and (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
– which we assume is always the last column.
The macro is now complete, and so we can re- enable the earlier Excel features,
which we earlier had disabled during the execution of the macro.
Let’s save and close out of VBA Editor,
and 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.