Excel VBA ActiveCell Offset Property (13 Common Uses)

Get FREE Advanced Excel Exercises with Solutions!

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.

Moving to the developer tab and clicking on the visual basic button

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

Inserting module in the visual basic for applications window

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
ARGUMENT REQUIRED/OPTIONAL EXPLANATION
RowOffset Required The number of rows to offset.
  • Positive value offsets downward
  • Negative value offsets upward
  • Default value is 0
ColumnOffset Optional The number of columns to offset.
  • Positive value offsets to the right
  • Negative value offsets to the left
  • Default value is 0

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

Overview of the dataset showing item, location, date, and sales column

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.

Using Excel VBA ActiveCell Offset Select method to move from B4 cell to D5 cell

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.

VBA code for ActiveCell Offset Select method

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

Using ActiveCell Offset Value method to enter value in the blank C5 cell

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

 VBA code for ActiveCell Offset Value method

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

Using ActiveCell Offset and Range object to select B4:E13 range

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

VBA code for using ActiveCell Offset and Range object

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

Copying and pasting range of cells with ActiveCell Offset and PasteSpecial method

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.

VBA code for copying and pasting range of cells with ActiveCell Offset and PasteSpecial method

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

Entering data into adjacent cells with ActiveCell Offset and Do while loop

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

VBA code entering data into adjacent cells with ActiveCell Offset and 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

Defining named range with ActiveCell Offset

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.

VBA code for defining named range with ActiveCell Offset

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.

Image showing the named range in the name manager window

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


7. Finding the Next Occurrence of a Value

Finding next occurrence of a value with ActiveCell Offset and Find method

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”.

VBA code for finding next occurrence of a value with ActiveCell Offset and Find method

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

Code Breakdown:

  • Initially, declare the variables “search_val” and “cell_loc” String and Range.
  • Assign the text “Alabama” in the search_val variable.
  • The VBA Offset method moves the ActiveCell down by one row and the Resize method searches all the cells until the last cell.
  • The VBA Find method to search for the specified value within the range and finds an exact match.
  • The If Then Else statement checks if the matching cell was found; if it is found the ActiveCell jumps to that location.
  • If the matching cell is not found, a MsgBox appears with the prompt “The value was not found”.

8. Summing a Range of Cells with ActiveCell.Offset

Calculating the sum of a range with ActiveCell Offset and Sum function

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

VBA code for calculating the sum of a range with ActiveCell Offset and Sum function

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

Code Breakdown:

  • The “Sum_Range” sub-program selects cell B4 using the Range property.
  • Then, using the Offset method this selection is extended 11 rows below and 3 columns to the right.
  • The WorksheetFunction object’s Sum function adds the values in the E5:E13 cells and writes the result in the selected E15 cell.

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

Counting the number of occurrences of a text with ActiveCell Offset and CounIf function

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.

VBA code for counting the number of occurrences of a text with ActiveCell Offset and CounIf function

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

Code Breakdown:

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


9.2 Counting for Multiple Conditions (OR Criteria)

Counting the number of occurrences of a text with multiple or criteria using ActiveCell Offset and CounIf function

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

VBA code for counting the number of occurrences of a text with multiple or criteria using ActiveCell Offset and CounIf function

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

Code Breakdown:

  • By using the addition (+) operator with the WorksheetFunction.CountIf we can apply the OR criteria to either count the occurrence of the text “Texas” or “Colorado” in the C5:C13 cells.

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

Calculating the sum with numeric condition using ActiveCell Offset and SumIf function

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

VBA code for calculating the sum with numeric condition using ActiveCell Offset and 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

Code Breakdown:

  • The SumIf function of the WorksheetFunction object adds the values in the E5:E13 cells that meet a certain condition. The condition is specified as “>3500” which selects only those cells in the range that have a value greater than “$3500”.

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


10.2 Calculating the Sum Using Date Criteria

Calculating the sum with date condition using ActiveCell Offset and SumIf function

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.

VBA code for calculating the sum with date condition using ActiveCell Offset and 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

Code Breakdown:

  • The SumIf function adds the “Sales” values in the E5:E13 cells based on the condition that the “Date” values in the D5:D13 range are greater than “9/1/2023”.

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

Formatting cells based on condition using ActiveCell Offset and for each loop

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”).

VBA code for formatting cells based on condition using ActiveCell Offset and for each loop

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

Code Breakdown:

  • The VBA For Each loop iterates through each cell E5:E13 range.
  • Nested into the For Each loop, the If statement checks if the cell value is greater than “$3000”.
  • If the condition holds, ActiveCell.Offset method selects the matched cell.
  • Using the With statement, the Interior.Color property changes the cell color with the RGB function. The color code of the cell is RGB(198, 224, 180).

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

Deleting rows based on condition using ActiveCell Offset and Do while loop and If else statement

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

VBA code for deleting rows based on condition

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

Code Breakdown:

  • The integer variable “i” checks from row 5, since row 4 is the column header.
  • The Do While loop iterates through each row in column E until it encounters a blank cell.
  • The If statement checks if the value in column E is less than $3000, if true the Row.Delete property ActiveCell.Offset method deletes the entire row.

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


13. Retrieving Values with ActiveCell.Offset and VLookup Function

Retrieving value with ActiveCell Offset and VLookup Function

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

VBA code for retrieving value with ActiveCell Offset and VLookup Function

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)

Applying Range Offset property to move selected cell

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.

VBA code for applying Range Offset property to move selected 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

Applying Cells Offset property to move selected cell

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

VBA code for applying Cells Offset property to move selected cell

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.

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.

Tags:

Eshrak Kader
Eshrak Kader

Eshrak Kader is a dedicated professional with a BSc. Degree in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology. He boasts a rich background in Microsoft Office Suite, and over the past year, he has authored over 120 insightful articles for the ExcelDemy. Currently, Eshrak leads a team of 5 in the ExcelDemy Video project and enjoys problem-solving and making videos on Excel. Eshrak’s passion for continuous learning underscores his commitment to excellence in... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo