When we automate the Excel worksheets by using Visual Basic (VB) codes, we frequently have to make the cursor moved to pre-determined locations to do calculations, copy, cut, paste, sort, and filter data. If you know how to combine the movements with the looping, you have an ultimate tool for automation. The payoff is tremendous and well worth the time to acquire such skill.
You can start with recording a macro (VB module) by using the “Relative References” command. Here are the steps:
- Click the “View” tab on the ribbon
- Click the triangle under the “Macros” and choose the “Use Relative References” command.
- Then click the “Record Macro…” to start recording your VB module.
When you select the “Use Relative References” command, the VB codes will be recorded as a relative reference instead of absolute reference to the cell that is activated. For example, if you want to copy the data from cell A1 to C1, the VB codes will be recorded as cell C1 (Range(“C1”)) as the paste location if you do not choose the “Use Relative References” command. If you choose the “Use Relative References” command, the VB codes will be recorded as ActiveCell.Offset(0, 2) instead. The difference is that if you use the absolute reference, your data will always be pasted to cell C1, no matter which cell your copy data is from. However, when you use relative reference, the paste cell location depends on the copy cell location. For example, if my copy cell location is now A4, the paste cell location will now be C4. The paste cell location will always be 2 cells on the right of the copy cell location. The use of “Use Relative References” command will give your macro far more flexibility than using the absolute reference. However, if you know the VB codes, it may be a lot faster just to type them in than recording them.
The OFFSET function is the tool for basic cursor movements in VB codes. The syntax is OFFSET(row reference, column reference). Please see the below examples.
- Move cursor to one cell right
Sub MoveRight()
ActiveCell.Offset(0, 1).Select
End Sub
- Move cursor to one cell left
Sub MoveLeft()
ActiveCell.Offset(0, -1).Select
End Sub
- Move cursor to one cell up
Sub MoveUp()
ActiveCell.Offset(-1, 0).Select
End Sub
- Move cursor to one cell down
Sub MoveDown()
ActiveCell.Offset(1, 0).Select
End Sub
- Move cursor diagonally to one cell right and one cell down
Sub MoveDiagonal()
ActiveCell.Offset(1, 1).Select
End Sub
- Move cursor diagonally to one cell left and one cell up
Sub MoveDiagonal2()
ActiveCell.Offset(-1, -1).Select
End Sub
Change the number of the row or column reference to move the cursor farther away. For example, ActiveCell.Offset(0, 5) will move the cursor 5 cells right of the current active cell.
Another set of cursor movements involve the use of the END function in VB codes. The use of the END function is the same as using the END key on your keyboard. The logics of movement are as below. For illustration purpose, I use the End(xlToRight) function, the xlToLeft, xlUp, and xlBottom share the same logics of movement as xlToRight.
- If the row is completely empty, the Visual Basic Codes will move the cursor to the end of the row.
- If the row has data and you place the cursor on the cell with data, the Visual Basic Codes will move to the last cell with data on the right end of the row.
- If the row has data and you place the cursor on an empty cell, the Visual Basic Codes will move to the right to search for the first cell with data.
- If the row has data and you place the cursor on the last cell with data, the Visual Basic Codes will move to the right to search for the first cell with data of another data segment. If there are no more data on the right, the Visual Basic Codes will move the cursor to the end of the row.
Below are the codes will move the cursor to right, left, top, and bottom.
Sub MoveRightEnd()
ActiveCell.End(xlToRight).Select
End Sub
Sub MoveLeftEnd()
ActiveCell.End(xlToLeft).Select
End Sub
Sub MoveToTop()
ActiveCell.End(xlUp).Select
End Sub
Sub MoveToBottom()
ActiveCell.End(xlDown).Select
End Sub
If you have multiple worksheets in a file (workbook), you may need to jump from one worksheet to another. Below are the codes that do the job.
Sub MoveToNextWorksheet()
ActiveSheet.Next.Select
End Sub
Sub MoveToPreviousWorksheet()
ActiveSheet.Previous.Select
End Sub
Sub MoveToFirstWorksheet()
Worksheets(1).Select
End Sub
Sub MoveToLastWorksheet()
Worksheets(Worksheets.Count).Select
End Sub
Sub MoveToSpecifiedWorksheet()
WorksheetNameSelected = Range(“WorksheetName”).Value
Sheets(WorksheetNameSelected).Select
End Sub
If you want to select a range of cell for copy, cut, and paste, you can use the below VB codes to do the trick. The key is to identify the beginning and ending cell of the range and then select them.
Sub SelectRange()
ActiveCell.Select
BeginCell = ActiveCell.Address
ActiveCell.End(xlDown).Select
EndCell = ActiveCell.Address
Range(BeginCell, EndCell).Select
End Sub
If you want to select an entire worksheet, you can use this VB codes. I usually use this VB codes to select and then erase all the previous data before pasting new ones on the worksheet.
Sub SelectEntire()
Cells.Select
End Sub
If you need to copy data from one file (workbook) and paste to another file, you can use the below VB codes.
Sub BetweenFiles()
ThisWorksheetName = Range(“ThisWorksheet”).Value
AnotherWorksheetName = Range(“AnotherWorksheet”).Value
Windows(AnotherWorksheetName).Activate
‘you can do calculation, copy and cut data
Windows(ThisWorksheetName).Activate
‘you can paste data here, do sorting, filtering, etc.
End Sub
You can download this file (Movement by using Visual Basic Codes.xlsm) which has the examples and VB codes that listed in this article.
