In the past couple of months of working on Excel and VBA Macros, I have been exposed to a few challenges which in the course of time, I have overcome. So, I thought I would share them with my beloved readers. This section: Excel Tips aims at sharing the small tips and tricks that I have come across which makes life easier for anyone working with Excel or VBA Macros. Cheers!

Here we’ll be focussing on how to select a range dynamically and perform further operations to it etc.
For e.g. If we have a Sheet “Input” of which one cell is required say column B, and we need to perform some kind of mathematical operation on them – viz. average, sum, etc. we need to do that by selecting the entire range of non-empty values of those two cells.

The problem here is that the number of entries in column A will vary from time to time, but we want a command button which automatically knows the amount of non-empty values and accordingly runs the mathematical operation. This is known as Dynamic Range Selection.

In other words, in case 1, if Cell B has values from B2 to B300, The Average formula should read: =AVERAGE(B2:B300), and in case the number of entries increases, the second input parameter in the average formula should change. The way to overcome this is a simple VBA code which records the number of entries in a particular cell and stores in a variable defined as a Long. This is:

mlngRange = CLng(Sheets("Input").Range("B2", Range("B2").End(xlDown)).Count)

Here, mlngRange is the Long variable in which the number of values in cell B from B2 onwards is recorded. Please remember if the declaration of mlngRange is done as a Long, include CLng as VBA by default, processes output as a String.

If the start point of selection is to be changed from B2 to say B4, this needs to be edited in the above code.

Limitation: The limitation of this code is that it recognises the first empty value in a cell, and considers it as the end-point. In case of column B having a few empty values in between, the remaining cell values will not be counted in mlngRange.

Hence, the next code is necessary. This code is unlike the previous one, which counts in a downward direction from the top columns to the bottom ones. Instead, this code counts in an upward direction, from the last non-empty cell onwards. The code is:
mlngRange = CLng(Worksheets("Input").Cells(Rows.Count, 2).End(xlUp).Row) - 1

Here, the ’2′ denotes the Column. As in A=1, B=2, etc. The (-1) in the end denotes that the first value in Column B should not be considered as in this case, Cell B1 is a label. If the count is to be considered from B2, this should be replaced by (-2)  and so on.

So what after storing the value of the range in a range variable?

Performing mathematical operations:

The output formula can be:

ActiveCell.Formula = "=AVERAGE(B2:B" & mlngRange + 1 & ")"

Please let me know if you face any issues with this. Cheerio!