Excel VBA ActiveCell Offset Property (13 Common Uses)

Excel VBA enables users to automate processes and increase productivity. Working with huge datasets and needing to modify cells in close proximity to the current selection is a typical scenario. The Excel VBA ActiveCell.Offset Property is useful in this situation. Without having to choose cells manually, we can transfer the selection to another cell based on a specified row and column offset by using ActiveCell.Offset.

In this article, we’ll go through the fundamentals of utilizing ActiveCell.Offset, then provides basic examples of how to move, select, enter, and copy data in relation to the current cell. Once we have a clear understanding of the basics, we’ll combine the Sum, CountIf, SumIf, and VLookup functions to perform various operations.

The above video is an overview of this article, which represents how to use ActiveCell.Offset in Excel VBA. Here, we’ve used the Worksheet.Sum function to calculate the total Sales and used the ActiveCell.Offset method to return the result in the E15 cell.

How to Launch VBA Editor in Excel

The Developer tab contains the VBA applications including creating and recording macros, Excel Add-ins, Forms controls, importing and exporting XML data, etc. This is our starting point for running VBA codes; just follow along.

📝 Note: By default, the Developer tab remains hidden. You can learn to enable the Developer tab by following this linked article.

Once enabled, move to the Developer tab, then click on the Visual Basic button in the Code group.

This launches a new window that looks familiar to other Microsoft products. Now, to run the VBA code, we need to insert our code somewhere. So, click the Insert tab and choose Module from the list. Afterward, we get a small Module window to insert our VBA code, and we hit the Run button or F5 key to execute the code.

The advantage of inserting the code in the Module is that we can apply this code to all the worksheets in this Excel workbook. Conversely, we can make our code only available for a specific worksheet.

Introduction to Application.ActiveCell Property in Excel VBA

In Excel VBA, the Application.ActiveCell property returns a range object that represents the active cell in the selected window.

• Syntax

expression.ActiveCell

expression is a variable representing the Application object.

• Remarks

The ActiveCell property returns the current cell in the active window if no object qualifier is given. It is important to differentiate between the selection and the active cell. The ActiveCell represents a single cell in the current selection, so even if we select multiple cells, there is only one ActiveCell.

Introduction to Range.Offset Property in Excel VBA

The Range.Offset returns an offset range from the chosen range represented by a VBA range object.

• Syntax

expression.Offset (RowOffset, ColumnOffset)

expression variable refers to a Range object.

• Arguments

Excel VBA ActiveCell Offset Property: 13 Cases

In this portion, we’ll apply the ActiveCell.Offset property in Excel VBA. For each method, we’ll start with a brief description of the dataset, where applicable, then show an overview of the result, including the VBA code explanation, and lastly show a live demonstration of running the VBA code. So, let’s see each example in detail with the appropriate illustration.

1. Moving ActiveCell to Another Cell with VBA ActiveCell.Offset.Select Method

Let’s introduce our dataset first. We have the Fruit Sales dataset shown in the B4:E13 cells, which depict the Item, Location, Date, and Sales columns respectively.

In this situation, we’ll use ActiveCell.Offset.Select method to move the active cell from the B4 cell to the D5 cell. We’ve set the RowOffset argument value to 1 and the ColumnOffset argument value to 2. This entails that the ActiveCell will move 1 row in the downward direction and 2 columns to the right.

To apply this VBA code, follow the steps shown previously to insert a Module. To do this, insert the code into the Module window >> press the Run button or hit the F5 key.

Sub Select_method()
'select cell 1 row below and 2 columns right
ActiveCell.Offset(1, 2).Select
End Sub

The video below shows the steps to move the ActiveCell using the Select method.

2. Entering Cell Value with VBA ActiveCell.Offset.Value Method

With this VBA macro, we can insert text data into the blank C5 cell by offsetting the value.

The ActiveCell.Offset.Value method inserts the text “Colorado” in the C5 cell which is 1 row below and 1 column to the right of the currently active B4 cell.

Sub value_method()
'insert value 1 row below and 1 column right
ActiveCell.Offset(1, 1).Value = "Colorado"
End Sub

The embedded video demonstrates the process of inserting a value into a blank cell using ActiveCell.Offset.Value method.

3. Selecting Range Using ActiveCell.Offset and Range Object

We can select a continuous range of cells by combining the ActiveCell.Offset and Range objects.

The “Selecting_Range” sub-routine selects a range of cells starting from 9 rows below the current ActiveCell and extending 3 columns to the right of the ActiveCell.

Sub Selecting_Range()
'selects cells 9 rows below and 3 columns right
Range(ActiveCell.Offset(9, 0), _
ActiveCell.Offset(0, 3)).Select
End Sub

You can watch the video, where we can select a range of cells by combining the ActiveCell.Offset and Range objects.

4. Copying a Range of Cells

Sometimes we want to copy our dataset to an adjacent cell, and with this VBA code, we can copy the data in the B4:E13 range and paste it into the G4:J13 cells.

This VBA macro copies the B4:E13 range of cells and pastes it into a new location which is 5 columns to the right of the currently ActiveCell. The PasteSpecial method pastes the values with all formatting and styling of the source theme.

Sub Copy_Range()
'copy and paste 5 columns right
Range("B4:E13").Copy
ActiveCell.Offset(0, 5).PasteSpecial _
xlPasteAllUsingSourceTheme
End Sub

The video below shows all the steps described so far.

5. Entering Data into Adjacent Cells

We can fill up the adjacent cells with the same text data using ActiveCell.Offset.Value method and a Do While loop.

The Do While loop inserts the text “Delivered” 3 into the cell adjacent to each non-empty cell in the columns. The ActiveCell.Offset.Value method offsets the text values 3 columns to the right and 1 row down from the current ActiveCell.

Sub Enter_Data_Adjacent_Cells()
'loop and insert value until active cell is empty
Do While ActiveCell.Value <> ""
ActiveCell.Offset(1, 3).Value = "Delivered"
ActiveCell.Offset(1, 0).Select
Loop
End Sub

At this point, see the video, which recaps all the steps to run this code.

Read More: Excel VBA Offset Within Loop

6. Creating a Dynamic Named Range

Named Ranges are a useful feature of Excel and we can make them using the VBA macro shown above. In this case, we’ve made a Named Range that refers to the Sales column. Selecting the E4:E13 cells we can see the Sales Named Range in the Name Box.

This macro defines a Named Range by choosing 3 columns to the right of the B4 ActiveCell and selecting all the cells until the last row. The Named Range is given the name Sales.

Sub Dynamic_Named_Range()
'move 3 columns right, select cells and name range
Range("B4").Select
ActiveCell.Offset(0, 3).Activate
Range(Selection, Selection.End(xlDown)). _
Name = "Sales"
End Sub

Observe the steps in the video to make a dynamic Named Range.

Lastly, we can go to Formulas >> Name Manager where we’ll find Sales Named Range.

7. Finding the Next Occurrence of a Value

Consider the situation where we have the text “Alabama” occurring multiple times in a column and we want to find each occurrence of this word. Each time we run this VBA program, the ActiveCell moves to the next instance of the text “Alabama”.

We move the ActiveCell to the column header and run this code, if the target text exists in that column the ActiveCell jumps to that cell otherwise, the program displays a message that the value was not found.

Sub Find_Next_Occurrence()
Dim search_val As String
Dim cell_loc As Range
'value to look for
search_val = "Alabama"
'look in every row using Find method
Set cell_loc = ActiveCell.Offset(1, 0).Resize _
(ActiveSheet.Rows.Count - ActiveCell.Row, 1).Find _
(search_val, LookIn:=xlValues, LookAt:=xlWhole)
'if found select value else return message
If Not cell_loc Is Nothing Then
cell_loc.Select
Else
MsgBox "The value was not found"
End If
End Sub

8. Summing a Range of Cells with ActiveCell.Offset

We can perform a summation of a range and then return the result in a desired location using ActiveCell.Offset.Value method.

The “Sum_Range” sub-program utilizes the VBA WorksheetFunction.Sum to return the total “Sales”. In a similar way, we can replace the VBA Sum function with VBA Average function to obtain the average sales.

Sub Sum_Range()
'move 11 rows below 3 columns right and sum values
Range("B4").Select
ActiveCell.Offset(11, 3).Value _
= WorksheetFunction.Sum(Range("E5:E13"))
End Sub

You can watch the video, where we demonstrate running the VBA code to compute the sum of a range.

9. Applying ActiveCell.Offset and CountIf Function in Excel VBA

This portion shows how to combine the ActiveCell.Offset method and VBA Countif function to count the occurrence of a text with single criteria and multiple (OR) criteria.

9.1 Counting the Number of Occurrences of a Text

Suppose we want to count the number of times the text “Alabama” occurs in a column. To make our lives easier, we can use this VBA code to count the number of occurrences.

The “Count_Text_Occurrence” sub-program uses the VBA WorksheetFunction.CountIf to count the occurrence of the text “Alabama”.

Sub Count_Text_Occurrence()
'move 11 rows below 3 columns right and count text
Range("B4").Select
ActiveCell.Offset(11, 3).Value = _
WorksheetFunction.countif(Range("C5:C13"), "Alabama")
End Sub

The video below shows the steps to count the occurrence of a text.

9.2 Counting for Multiple Conditions (OR Criteria)

We can develop our VBA code to count the instances where either the text “Texas” or “Colorado” is present; the OR criteria.

The “count_mutiple_condition” macro counts the occurrence of either the text “Texas” or “Colorado”.

Sub count_mutiple_condition()
''move 11 rows below 3 columns right and count texts
Range("B4").Select
ActiveCell.Offset(11, 3).Value = _
WorksheetFunction.countif(Range("C5:C13"), "Texas") _
+ WorksheetFunction.countif(Range("C5:C13"), "Colorado")
End Sub

Follow the live demonstration to see the process of counting for multiple OR criteria.

10. Utilizing ActiveCell.Offset and SumIf Function in Excel VBA

In this section, we’ll apply the VBA SumIf function and the ActiveCell.Offset method to sum the “Sales” values based on the provided condition.

10.1 Obtaining the Sum with Numeric Condition

Here, we’ve aggregated the “Sales” that exceed “$3500” using the VBA SumIf function.

The “Sum_Numeric_Condition” sub-routine performs summation in the E5:E13 range that satisfies the condition of “Sales” greater than “$3500”.

Sub Sum_Numeric_Condition()
'move 11 rows below 3 columns right and sum values
Range("B4").Select
ActiveCell.Offset(11, 3).Value _
= WorksheetFunction.SumIf(Range("E5:E13"), ">3500")
End Sub

See how to sum values based on numeric conditions by watching the live presentation.

10.2 Calculating the Sum Using Date Criteria

Likewise, we can aggregate the “Sales” that have taken place after a certain date “9/1/2023” with the help of the VBA SumIf function.

The “Sum_Date_Condition” considers the D5:D13 range to check the dates after the specified date “9/1/2023” and calculates the sum total of the “Sales” from the E5:E13 range.

Sub Sum_Date_Condition()
'move 11 rows below 3 columns right and sum values
Range("B4").Select
ActiveCell.Offset(11, 3).Value = _
WorksheetFunction.SumIf(Range("D5:D13"), _
">" & "9/1/2022", Range("E5:E13"))
End Sub

The process of utilizing the VBA SumIf to get the sum based on date criteria is shown in the accompanying video.

11. Employing ActiveCell.Offset to Format Cells Based on a Condition

VBA’s ActiveCell.Offset method can be employed to format the cell color based on a condition (if the cell value is greater than “$3000”).

In this code snippet, the If Else statement checks whether the values in the E5:E13 range exceed “$3000”. If true, the cell color is changed to a green color.

Sub Format_Cells()
Dim cell As Range
'check if cell value > $3000, if so apply color
For Each cell In Range("E5:E13")
If cell.Value > 3000 Then
cell.Select
ActiveCell.Offset(0, 0).Select
With Selection.Interior
.Color = RGB(198, 224, 180)
End With
End If
Next cell
End Sub

The procedure to format the cell color based on a condition is displayed in the following video.

12. Deleting Rows Based on Condition Using VBA ActiveCell.Offset

We can also delete rows based on a condition (cell value is greater than “$3000”) using ActiveCell.Offset’s Row.Delete property.

The VBA code uses the Do While and If Else statements to check the values in column E. If this value is less than “$3000”, then the program deletes those rows. It is important to note that i=5 refers to the dataset values since row 4 contains the column headers.

Sub Deleting_Rows()
Dim i As Integer
'start from row 5 since row 4 is the header
i = 5
'check if col E values are less then $3000, if so delete rows
Do While Range("E" & i).Value <> ""
If Range("E" & i).Value < 3000 Then
Range("E" & i).Select
ActiveCell.Offset(0, 1).EntireRow.Delete
Else
i = i + 1
End If
Loop
End Sub

The following video shows how to delete the cells based on a condition.

13. Retrieving Values with ActiveCell.Offset and VLookup Function

This code runs the VBA VLookup function to locate the “Avocados” sales figure in the B4:E13 range.

The VBA VLookup function checks the B4:E13 range to check for “Avocados” and returns the corresponding “Sales” figure.

Sub VLookup_Values()
'lookup Avocados and return sales figure
Range("B4").Select
ActiveCell.Offset(11, 3).Value _
= WorksheetFunction.VLookup("Avocados", Range("B4:E13"), 4, 0)
End Sub

You can watch the video to see how to retrieve values with ActiveCell.Offset and the VLookup function.

How to Use Range.Offset Property in Excel VBA (ActiveCell.Offset Alternative)

We have already described how to move to another cell using ActiveCell.Offset, but we can also use the Range.Offset.Select to move to another cell.

Here, the Range(“B4”) selects the B4 cell while, the Offset(1, 2) moves the selection 1 row below and 2 columns to the right of the original cell. The Select method selects that cell in the worksheet.

Sub Range_Offset_Property()
'select cell 1 row below and 2 columns right
Range("B4").Offset(1, 2).Select
End Sub

You can observe the process in the video shown below.

How to Apply Cells.Offset Property in Excel VBA

Another way to shift cells involves using the Cells.Offset property.

For instance, the Cells(4, 2) select the B4 cell, and the selection is then moved 1 row below and 2 columns to the right using the Offset method.

Sub cells_offset_property()
Cells(4, 2).Offset(1, 2).Select
End Sub

See the application of Cells.Offset property in the live demonstration.

Read More: Excel VBA Offset Column

Frequently Asked Questions

• What is the ActiveCell property in Excel VBA?

In Excel VBA, the active worksheet’s presently selected cell is referred to as the ActiveCell property.

• What is the Offset method in Excel VBA?

The VBA Offset method jumps from the active cell to a new cell by setting a row and column offset from the presently active cell.

• How do you use the Offset method with a negative offset in Excel VBA?

Simply provide a negative value for the RowOffset or ColumnOffset. For instance, in the following code, we can move the active cell one row upward

ActiveCell.Offset(-1, 0). Select

• How do I Offset one column left in VBA?

To Offset one column to the left we can set the RowOffset to 0 and ColumnOffset to -1. Here is a simple example code for Offsetting the selected column one cell to the left.

Sub OffsetColumnLeft()
' Get the currently selected column
Dim selectedColumn As Range
Set selectedColumn = Selection.EntireColumn
' Offset the column one cell to the left
selectedColumn.Offset(0, -1).EntireColumn.Select
End Sub

Things to Remember

• The ActiveCell attribute identifies the presently chosen cell in the active worksheet.
• Depending on which way you wish to change the selection, the row and column Offset values can be either positive or negative.
• It’s crucial to confirm that the generated cell or range of cells falls within the worksheet’s boundaries when utilizing the Offset Otherwise, an error may occur.
• The Offset method can be used to iterate through a cell range and perform an action on each cell.

Wrapping Up

• The basic usage of ActiveCell.Offset method involves moving selection, entering a value into blank a cell, copying, and entering data into a range of cells.
• Advanced application of ActiveCell.Offset method means defining Named Ranges, using the VBA Find method to find instances of a text, combining various functions like Sum, CountIf, SumIf, and VLookup functions to obtain the desired outcome.

Download Practice Workbook

You can download the following practice workbook to practice yourself.

Conclusion

In essence, this article explains how to use Excel VBA ActiveCell.Offset Property to move, select, write, copy, and delete data from a specific cell or range to a new location relative to its current position. In addition, we’ll also learn to combine the Offset property with popular and handy functions like Sum, CountIf, SumIf, VLookup to perform various operations. Moreover, we’ve also shown the alternatives to ActiveCell.Offset by using the Range.Offset and Cells.Offset properties. Lastly, there are questions and answers to the most popular queries regarding the ActiveCell.Offset in VBA.

We are hopeful that this article has provided you with a clear understanding of ActiveCell.Offset in Excel VBA. Armed with this knowledge, readers can now confidently apply this useful feature in their own VBA projects. If you have any suggestions or comments, don’t forget to share them with us.