As a regular Excel user, you may use the Cursor Movement Keys to move one cell to right in Excel. But Excel provides different ways to solve a certain problem. We can use VBA codes to move one cell to right in Excel. This article will discuss every possible example to move one cell to right using VBA in Excel. I wish you enjoy this article and gain some valuable knowledge.
How to Move One Cell to Right Using VBA in Excel: 3 Examples
Instead of Cursor Movement Key, we use three different VBA codes to move right one cell. All three methods mainly utilize the Offset method. Which helps to navigate from one cell to another. To navigate, you can either select one cell or a range of cells. It provides access to the properties. All three methods are fairly easy to digest. To show all the VBA methods, we take a dataset with several names on it.
1. Move One Cell to Right Using Range Property
Our first method is mainly based on the Range with Offset. Here, you select a cell range or a range of cells first and then provide an offset to move right to one cell.
Steps:
- To apply the VBA code, you need to open the Developer tab. You can open it by pressing Alt+F11 or you can customize your ribbon section and get the Developer tab. This will open up the Visual Basic interface.
- Now, go to the Insert tab and select Module.
- A Module code window will occur. In there, copy the following code and paste it.
Sub VBA_range_offset()
Range("B5").Offset(0, 1).Select
End Sub- Now, close the Visual Basic code window.
- Select cell B5.
- Go to the View tab in the Ribbon and select Macros.
- A Macro dialog box will appear. Select VBA_range_offset from the Macro Name and click on Run.
- That will move your selection from cell B5 to cell C5.
Breakdown of the Code
Range(“B5”).Offset(0, 1).Select
First, select a range from where you want to do your further work. Then, provide an offset property and specify the argument. Offset has two arguments row and column. As we want to move right one cell, we keep the row value 0 and put the column value 1 to move one cell. Here, you can place different row and column arguments to shift to a different place.
2. Using ActiveCell Property
Secondly, we can move right one cell by using ActiveCell with Offset. This method helps to move any active cell to the one cell right.
Steps:
- Open the Developer tab by pressing Alt+F11.
- Now, go to the Insert tab and select Module.
- In the Module code window, copy the following code and paste it there.
Sub VBA_ActiveCell_offset()
ActiveCell.Offset(0, 1).Select
End Sub- Close the Module code window.
- Select cell B6.
- Now, go to the View tab and select Macros.
- A Macro dialog box will appear. Select VBA_ActiveCell_offset from the Macro Name and click on Run.
- That will move from cell B6 to cell C6.
Breakdown of the Code
ActiveCell.Offset(0, 1).Select
ActiveCell provides an option where you can select any cell from our worksheet. Offset has two arguments row and column. As you want to move one cell right, so, we put row argument as 0 and column argument 1. This code will help to move any active cell in your worksheet and provide a dynamic offset.
Read more: How to Move Down One Cell Using Excel VBAÂ
3. Selection Property to Move One Cell to Right in Excel
Lastly, we can move one cell right using Selection with Offset. Just like the ActiveCell method, this is also a dynamic offset method. Here, you can select any cell, it will move one cell right.
Steps:
- Open the Developer tab by pressing Alt+F11.
- Go to the Insert tab and select Module.
- In the Module Code window, copy the following code and paste it.
Sub VBA_selection_offset()
Selection.Offset(0, 1).Select
End Sub- Close the Module code window.
- Select cell B8.
- Go to the View tab in the ribbon and select Macros.
- A Macro dialog box will appear. Select VBA_selection_offset from the Macro Name and click on Run.
- This will shift one cell right from cell B8 to cell C8.
Breakdown of the Code
Selection.Offset(0, 1).Select
Here, two arguments of Offset help you to move one cell right. To do this, we put the row as 0 and column 1. Selection provides a dynamic situation where you can select any cell and apply this code, it will eventually move it to one cell right.
Read More: How to Shift Cells Right in ExcelÂ
Download Practice Workbook
Download this practice workbook.
Conclusion
We have discussed three different VBA codes to move one cell right. Expect the first method, all the codes provide a dynamic offset solution which is really amazing to use. I hope you enjoy this article and gain a lot of knowledge about VBA codes.
Related Articles
- How to Shift Cells Up in Excel
- How to Shift Cells Down in Excel
- How to Shift Cells Down in Excel without Changing Formula
- Fix: Excel Cannot Shift Nonblank Cells
- How to Tab to Certain Cells in Excel
- How to Perform Double Click Cell Jump in Excel
<< Go Back to Move Cells | Excel Cells | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
