VBA Select Cell

VBA Select Cell

MS Excel provides several VBA inbuilt functions one of them is the Select a Cell Function, which is used to select a cell from the worksheet. There are two methods to select a cell by the Cell another is the Range. It can be used as the as a part of the formula in the cell. A cell is a property in the VBA, but Range is the Object, so we can use a cell with the range but cannot use the range with the cell.

As an example, if the user wants to give a reference for A5 then he can give by two way one is select a cell by the Cell (5,4) another is the Range (“A5”).

The Syntax of the Select Cell Function:

SELECT CELL () – It will return the value of the cell which is given in the reference. There are two ways to select a cell.

Ex: Select Cell function –

ActiveSheet.Cells(5, 4). Select

OR

ActiveSheet.Range("D5"). Select

How to Select Cell in Excel VBA?

We will learn how to select a cell in Excel Using VBA code with few examples.

VBA Select Cell – Example #1

How to use the basic VBA Select Cell Function in MS Excel.

A user wants to select a header cell which is C5 and User name(D5) in his workbook, after that print this name in the workbook which is given in the reference given cell is D5.

Let’s see how the Select Cell function can solve his problem. Follow the below steps to select a cell in excel VBA.

Step 1: Open the MS Excel, go to sheet1 where the user wants to select a cell and display the name of the user.

 

Step 2: Go to the Developer tab >> Click on the Visual Basic.

Step 3: Create one Select Cell_Example1() micro.

Code:

Sub Select_Cell_Example1()

End Sub

Step 4: Now activate sheet and select the user’s name cell by the Cells method.

Code:

Sub Select_Cell_Example1()

  Sheets("Sheet1").Activate
  Cells(5, 3).Select

End Sub

Step 5: Now select the User name cell which is D5 by Range method.

Code:

Sub Select_Cell_Example1()

  Sheets("Sheet1").Activate
  Cells(5, 3).Select
  Range("D5").Select

End Sub

Step 6: Now print the User name.            

Code:

Sub Select_Cell_Example1()

  Sheets("Sheet1").Activate
  Cells(5, 3).Select
  Range("D5").Select
  MsgBox "User's Name is " & Range("D5").Value

End Sub

Step 7: Click on the F8 button to run step by step or just click on the F5 button.

Summary of Example #1:

As the user wants to select the cells and display the value in that cell. He can achieve his requirement by Select cells and range method. Same we can see in the result.

VBA Select Cell – Example #2

How to use the VBA Select Cell Function with the Range in the MS Excel.

A user wants to select the cell Delhi which is B7 as the first cell of a range. So, by default, there is a data range which is A1 to C13. But the user wants to create his own range and from where he wants to select the first cell.

Let’s see how the Select Cell function can solve his problem. Follow the below steps to select a cell in excel VBA.

Step 1: Open the MS Excel, go to sheet2 where the user wants to select a cell and display the name of the user.

Step 2: Go to the developer tab >> Click on the Visual Basic.

Step 3: Create one Select Cell_Example2() micro and inside declare a string as the select_status.

Code:

Sub Select_Cell_Example2()

  Dim select_status As String

End Sub

Step 4: Now activate sheet, define a range from B7 to c13 and select the first cell in that defined range.

Code:

Sub Select_Cell_Example2()

  Dim select_status As String
  Sheets("Sheet2").Activate
  select_status = Range("B7:C13").Cells(1, 1).Select

End Sub

Step 5: Now print the status of selection if it is selected then it will be true otherwise false.

Code:

Sub Select_Cell_Example2()

  Dim select_status As String
  Sheets("Sheet2").Activate
  select_status = Range("B7:C13").Cells(1, 1).Select
  MsgBox "Selection Action True/False: " & select_status

End Sub

Step 7: Click on the F8 button to run step by step or just click on the F5 button.

Summary of Example #2:

As the user wants to define their own range and from where he wants to select the first cell. He can achieve his requirement by Select cells and range method. Same we can see in the result. As we can see in the result selection happed on Delhi which is the first cell of defined range by the user.

VBA Select Cell – Example #3

How to use the VBA Select Cell Function with the loop in the MS Excel.

A user wants to calculate how many employees record he has in the employee details table.

Let’s see how the Select Cell function can solve his problem. Follow the below steps to select a cell in excel VBA.

Step 1: Open MS Excel, go to sheet3 where the user wants to select a cell and display the name of the user.

Step 2: Go to the developer tab >> Click on the Visual Basic.

Step 3: Create one Select Cell_Example3() micro and inside declare an Integer as the i.

Code:

Sub Select_Cell_Example3()

  Dim i As Integer

End Sub

Step 4: Now activate sheet and start a for loop to count the number of the employees.

Code:

Sub Select_Cell_Example3()

  Dim i As Integer
  Sheets("Sheet3").Activate
  For i = 1 To 12
  Cells(i + 1, 5).Value = i
  Next i

End Sub

Step 5: Now print the Total employee records available in the table.

Code:

Sub Select_Cell_Example3()

  Dim i As Integer
  Sheets("Sheet3").Activate
  For i = 1 To 12
  Cells(i + 1, 5).Value = i
  Next i

MsgBox "Total employee records available in table is " & (i - 1)

End Sub

Step 7: Click on the F8 button to run step by step or just click on the F5 button.

Summary of Example #3:

As the user wants to calculate the number of employee’s record available in the employee table. He can achieve his requirement by Select cells in the for-loop method. Same we can see in the result. As we can see in the result the Total employee records available in the table is 12.

Things to Remember

• The defined range by the user is different from the regular range as we can see in Example #1.
• A cell is a property in the VBA, but Range is the Object, so we can use a cell with the range but cannot use the range with the cell.
• A user can pass the column alphabetic name also in cells like Cells (5,” F”) it is the same as the Cells (5, 6).
• Selecting a cell is not mandatory to perform any action on it.
• To activate a sheet a user can use sheet activate method as we have used in the above examples.

Recommended Articles

This has been a guide to VBA Select Cell. Here we discussed how to Select Cells in Excel using VBA along with practical examples and downloadable excel template. You can also go through our other suggested articles –

1. VBA 1004 Error
2. VBA Get Cell Value
3. VBA Color Index
4. VBA RGB