Introduction to Excel VBA Part 2

Introduction to Excel VBA Part 2

If you didn’t catch part one, click here.

In this blog we will explore other methods of selecting cells and ranges of cells.

Using Activecell
Using Offset
Selecting Data to the End

Using Activecell

You can use Activecell in place of a cell reference, to create a more dynamic script. Activecell is very useful when you need to reference the cell that you are in. In some circumstances such as selecting the cell after your last row of data, this will need to be dynamic.

The code below will select the current activecell.

Sub SelectActiveCell()
    
    ActiveCell.Select
    
End Sub
 

Using Offset

To be able to achieve the example mentioned above, you will also need to use the offset function. Offset allows you to offset a selected cell or activecell, by a number of rows, columns or both.

The code below will select the cell below the activecell.

Sub SelectCellBelowActiveCell()

    ActiveCell.Offset(1).Select
    
End Sub
 

The code below will select the cell to the right of the activecell.

Sub SelectCellBelowActiveCell()

    ActiveCell.Offset(,1).Select
    
End Sub

The code below will select the cell below and to the right of the activecell.

Sub SelectCellBelowActiveCell()

    ActiveCell.Offset(1,1).Select
    
End Sub

Selecting Data to the End

We explored selecting data using range() in VBA Part 1, however the range of data we manipulate won’t always be the same count of columns or rows, therefore in some scripts we will need to use a more dynamic method of selecting data.

The code below will select your data from the active cell to the right, stopping at the end of your data. (when excel detects a blank cell)

Sub SelectDataTopRow()

    Range(Selection, Selection.End(xlToRight)).Select
    
End Sub

The code below will select your data from the active cell down, stopping at the end of your data. (when excel detects a blank cell)

Sub SelectDataDown()

    Range(Selection, Selection.End(xlDown)).Select
    
End Sub

To learn more about Excel VBA keep your eyes out out for Part 3, or book onto one of our Excel VBA Courses.