Lesson 4 : Playing with Dynamic Ranges in Excel

In the previous post, we have covered the following topics :

What is a dynamic range?
It is a range that can be expanded or contracted in future versions of your spreadsheet. In other words, you cannot foresee the number of used cells in the range at the time of macro creation. It's a necessity while working with real data.

Task

To automate multiplication in excel
In column D, multiply column C values by 2

The data is shown in the image below :

Let's record a macro to generate code.

The Macro Recorder is a very useful tool when you want to automate a specific task, but don't know how to program it in Excel VBA.

See this tutorial : How to record your macro

The recorded code for the above task is as follows:

The problem with the recorded code shown above is that it was generated for a specific range (D3:D11).

Next Step : Make the above code work for dynamic range

We need to identify the last used row in column C so that we can use it to fill in data below in column D.

How to find last used row?

With ActiveSheet
last = .Cells(.Rows.Count, "C").End(xlUp).Row
End With

See this tutorial : Find last used row or column in excel VBA

The modified code is shown below :

Sub Dynamic()
Dim last As Double
With ActiveSheet
last = .Cells(.Rows.Count, "C").End(xlUp).Row
End With
Range("D3").Formula = "= C3 * 2"
Range("D3").AutoFill Destination:=Range("D3:D" & last)
End Sub

What is "Dim last As Long" ?

Dim is used to declare a variable
last is a variable name
Double is a data type for numeric variable.

What is "Range("D3:D" & last)" ?
It includes data from cell D3 to the last used row in column D.

Two Important Takeaways

Macro recorder does not create very efficient code. You can remove the words like: Select, Scroll. See the difference shown in the image below.

2. Macro recorder does not create code for a dynamic range.

Next Technique : End Property and Offset

OFFSET

The OFFSET function returns a cell that is a specified number of rows and/or columns from the reference cell.

The syntax for OFFSET is as follows :

Range("A1").Offset(1, 0).Select - It means you want to move one row down from cell A1. The cell selected will now be A2.

Range("A1").Offset(0, 1).Select - It means you want to move one column right to cell A1. The cell selected will now be B1.

Range("A1").Offset(1, 1).Select - It means you want to move one column right and one row down from cell A1. The cell selected will now be B2.

End Property

The END property is used to select a range from the active cell to the last entry in a row or column.

Range("A1048576").End(xlUp).Select - It is equivalent to pressing the CTRL + UP arrow.

Range("A1").End(xlDown).Select - It is equivalent to pressing the CTRL + DOWN arrow.

Range("XFD1").End(xlToLeft).Select - It is equivalent to pressing the CTRL + LEFT arrow.

Range("A1").End(xlToRight).Select - It is equivalent to pressing the CTRL + RIGHT arrow.

Task : Suppose you have data in column A starting from cell A1. You want to select a cell immediately to the right of the last used cell in column A

Sub Offset1()
Range("A1048576").End(xlUp).Offset(0, 1).Select
End Sub

Task : Suppose you have data in column A starting from cell A1. You want to select a cell immediately down from the last used cell in column A

Sub Offset2()
Range("A1").End(xlDown).Offset(1, 0).Select
End Sub

Task : Suppose you have data in row 1. You want to select a cell immediately down from the last used cell in row1

Sub Offset2()
Range("A1").End(xlToRight).Offset(1, 0).Select
End Sub

Task : Suppose you have data in row 1. You want to select a cell immediately to the right of the last used cell in row1

Sub Offset2()
Range("XFD1").End(xlToLeft).Offset(0, 1).Select
End Sub

Another technique to handle dynamic range : Looping
What is loop ?
It allows you to execute code repeatedly until a certain condition is reached.

Let's rewrite the recorded code using FOR NEXT loop.

The syntax for FOR NEXT loop is as follows :

For < Counter Variable>= <Initial Value> To <End Value>
<Code that you want to repeat through loop>
Next <Counter Variable>

The example is shown in the image below :

The modified code using FOR NEXT loop is shown below :

Sub Dynamic()
Dim last As Double, i As Integer
With ActiveSheet
last = .Cells(.Rows.Count, "C").End(xlUp).Row - 2
End With
For i = 1 To last
Range("D" & i + 2).Formula = Range("C" & i + 2).Value * 2
Next i
End Sub

Range("D" & i + 2) evaluates to Range("D"&1+2) i.e. Range("D3") in the first loop counter. Then the range increments by 1 till last used row.

Where to Go From Here

Well, this is it. By now, we have covered some basics and intermediate topics about VBA. I hope these four lessons have been effective for you, and have given you a good idea about excel macros.

Next Step : Practice

If you're learning to program, you should be programming. Reading basics about VBA is essential for beginners, but without practice, that reading means nothing.

The best way to learn VBA is to keep programming macros on a regular basis. Record Your Macro And Then Make It Efficient And Dynamic.

Google Is Your Friend !

A simple Google search about "Excel VBA" will give you the best materials or VBA code you are looking for.

Searching Tip : Include multiple key words to help narrow your search.

Join Excel Forums and Groups

The following is a list of forums and groups where you can discuss your excel VBA queries.

1 . MrExcel Forum

2. VBA Express Forum

3. Excel and VBA Users (LinkedIn Group)

4. Microsoft Excel Users (LinkedIn Group)

Previous : 35 Macros For VBA Beginners

About Author:
Deepanshu Bhalla

Deepanshu founded ListenData with a simple objective - Make analytics easy to understand and follow. He has over 10 years of experience in data science. During his tenure, he worked with global clients in various domains like Banking, Insurance, Private Equity, Telecom and HR.

While I love having friends who agree, I only learn from those who don't
Let's Get Connected Email LinkedIn