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.
- How to enable developer tab
- How to get started with VBA
- How to record your macro
- How to run macros
- How to insert a button to run macro
- Fundamentals of VBA Programming
- Write Your Own VBA Code - 35 Examples
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
The data is shown in the image below :
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
The modified code is shown below :
What is "Dim last As Long" ?
What is "Range("D3:D" & last)" ?
It includes data from cell D3 to the last used row in column D.
Two Important Takeaways
Next Technique : End Property and Offset
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.
Range("A1048576").End(xlUp).Offset(0, 1).Select
End Sub
Range("A1").End(xlDown).Offset(1, 0).Select
End Sub
Range("A1").End(xlToRight).Offset(1, 0).Select
End Sub
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 :
The modified code using FOR NEXT loop is shown below :
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
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.
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.
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 ASub 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 ASub 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 row1Sub 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 row1Sub 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>The example is shown in the image below :
<Code that you want to repeat through loop>
Next <Counter Variable>
The modified code using FOR NEXT loop is shown below :
Sub Dynamic()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.
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
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
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
2. VBA Express Forum
3. Excel and VBA Users (LinkedIn Group)
4. Microsoft Excel Users (LinkedIn Group)
I love this site ..:D
ReplyDeleteGood for beginners
ReplyDeleteOne of the best videos and tutorials.
ReplyDeletePlease share ready VBA codes up to 100 nos.
This was very informative. thanks
ReplyDeleteThe best VBA code tutorial
ReplyDelete