Select Cells and Ranges using VBA in Excel

We need to select cells and ranges using VBA to perform many functions on the excel spreadsheet. VBA provides an effective way to do this. We can easily select a single cell. Ranges can be of two types, fixed size range, and variable size range. This blog would teach you to select the last cell of a column or row. We would learn to select the range including the headers.

We have a number of Sub Procedures in the blog. You can copy and paste them into the VBA editor to practice yourself. Download the sample workbook below.

So let us start learning.

Sub Procedures in VBA

Using VBA in excel, we would use Sub procedures in workbook modules to select cells and ranges. This is the syntax for writing a Sub Procedure.

Sub Sub_Name()
Msgbox"Learning VBA with ExcelUnlocked"
End Sub

Here are common things to know for all the Sub Procedures we are about to write.

• We will place the code into the Worksheet Module.
• The Sub_Name after the Sub keyword is the name of the Sub Procedure.
• The lines of code between Sub and End Sub would be executed.
• Place the cursor anywhere between Sub and End Sub, and then press F5 to run the Sub Procedure.

Select a Single Cell

Use the following code to select a single cell of the active sheet.

Sub Select_Cell()
Range("D4").Select
End Sub

• Copy and Paste this code into a new workbook module in the VBA editor.

• Hold the Alt key and press the Tab key to switch between the Excell workbook and the VBA interface.
• Go to the Developer tab->Macros and run the macro named Select_Cell that we just wrote in the VBA editor. You can also press the Alt F8 key to open a list of macros.

Explanation

• Range helps us to specify the cell reference in inverted commas using Range(“A2”),. It tells VBA the address that we want to refer to.
• We used the select method with Range to select the pointed location. Range(“D4”).Select

Select the Last Cell in a Column

We would now learn to select the last cell of a column containing data with no blanks in between. If a blank cell occurs then the cell would become the last cell.

Use the following piece of code.

Sub Select_Last_Cell_Column()
Range("A1").End(xlDown).Select
End Sub

Explanation –

• The Range(“A1”) tells the address of the first cell of column A.
• Using the End Property with xlDown gets us the address of the last filled cell that contains data in column A until it encounters a blank cell. Range(“A1”).End(xlDown). A7 is the last filled location and there is a blank cell A8 after that.
• Use the Select method to select the last contiguous location in column A.

Select the Last Filled Cell in a Row

We can get the address of the last filled cell in a row unit a blank cell is encountered.

Let us suppose we have the following data in the first row.

There is a blank cell E1 highlighted in green. Use this code to select the last cell of 1st row that has data.

Sub Select_Last_Cell_Row()
Range("A1").End(xlToRight).Select
End Sub

Explanation –

• The Range(“A1”) accesses the address of cell A1.
• The End property allows us to select the last filled cell. Here xlToRight tells the cell address to the right last filled cell that is D1.
• The Select Method selects cell D1.

Select a Fixed Sized Range

We can select any fixed size range, let us suppose A2: A10 with the help of the following code.

Sub Select_fix_range()
Range("A2:A10").Select
End Sub

• The Range can be supplied as A2:A10 to Range(“A2:A10”). The Select method will select the addressed range.

Select a Variable Size Range

Let us suppose we want to select the range of cells that is contiguous and has data with no blanks in between. Its size is not fixed as we add or remove data. Use this code to select a variable-sized range around cell A1.

Another method to select a range is as follows.

Range(<upper_left_cell>,<bottom_right_cell>).Select

Where the upper_left_cell and bottom_right_cell of a range are as follows.

We would make the upper_left_cell to be fixed as B3 while the bottom_right_cell would be dynamic. It would be the last filled cell to down and the last fill cell to the right of the upper_left_cell B3.

Use this code to select a variable-sized range from cell B3.

Sub Variable_Range_Select()
Range("B3", Range("B3").End(xlDown).End(xlToRight)).Select
End Sub

Now even if you add one more row 7th row and remove the column E data, you will see this to work as normal as the bottom right cell is dynamic.

Select Current Region around a cell

Use the CurrentRegion property for the Range object to access the block of cells around the cell specified in the Range object.

Use this code to select cells around cell B4,

Sub Select_CurrentRegion()
Range("B4").CurrentRegion.Select
End Sub

Explanation –

• The CurrentRegion property along with the select method selects the filled cells around the cell B4.

This is how we select cells and ranges in excel.

Thank you for reading.