How to Get Cell Value by Row and Column in Excel VBA

In this article, I’ll show you how you can get the cell value by row and column from a worksheet in Excel VBA. You’ll learn to get the cell value from the whole worksheet, as well as from the used range of the worksheet and a selected range.


Get Cell Value by Row and Column in Excel VBA (Quick View)

Sub Cell_Value_from_Whole_Worksheet()

Value = Worksheets("Sheet1").Cells(7, 3)

MsgBox Value

End Sub

VBA Code to Get Cell Value by Row and Column in Excel Worksheet


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


3 Methods to Get Cell Value by Row and Column in Excel VBA

Therefore, without further delay, let’s go to our main discussion today. We’ll learn to get the cell value by 3 methods today: from the whole worksheet, from the used range of the worksheet, and from a selected range.


1. Get Cell Value by Row and Column from the Whole Worksheet in Excel VBA

First of all, we’ll get the cell value by row and column from the whole worksheet.

To get the cell value by row and column from the whole worksheet, you can use the Cells method of VBA.

For example, to get the value from the cell in the 4th row and the 6th column of the worksheet called Sheet1, you can use:

Value = Worksheets("Sheet1").Cells(4, 6)

⧭ Example:

Here we’ve got a worksheet called Sheet1 with the names of some students and their marks in Physics, Chemistry, and Mathematics of a School. The data set starts right from cell A1 of the worksheet.

Data Set to Get Cell Value by Row and Column in Excel VBA

Now, to get the marks of the 6th student in Chemistry, you have to get the cell value from the 7th row and 3rd column of the worksheet.

The VBA code will be:

⧭ VBA Code:

Sub Cell_Value_from_Whole_Worksheet()

Value = Worksheets("Sheet1").Cells(7, 3)

MsgBox Value

End Sub

VBA Code to Get Cell Value by Row and Column in Excel Worksheet

⧭ Output:

Run the code. It’ll display the cell value from the 7th row and 3rd column of Sheet1, which is 78.

Read More: How to Find Value in Column Using VBA in Excel (4 Ways)


2. Get Cell Value by Row and Column from the Used Range in Excel VBA

Next, we’ll get the cell value by row and column from the used range of the worksheet.

To get the cell value by row and column from the used range of the worksheet, you can again use the Cells method of VBA, but along the UsedRange object.

For example, to get the value from the cell in the 4th row and the 6th column of the used range of the worksheet called Sheet2, you can use:

Value = Worksheets("Sheet2").UsedRange.Cells(4, 6)

⧭ Example:

Here we’ve got another worksheet called Sheet2 with the same data set, the names of some students and their marks in Physics, Chemistry, and Mathematics of a School. But this time the data set starts from cell B2 of the worksheet.

Data Set to Get Cell Value by Row and Column in Excel VBA

Now, to get the marks of the 6th student in Chemistry again, you have to get the value from the 7th row and the 3rd column of the used range.

The VBA code will be:

⧭ VBA Code:

Sub Cell_Value_from_Used_Ranget()
Value = Worksheets("Sheet1").UsedRange.Cells(7, 3)
MsgBox Value
End Sub

VBA Code to Get Cell Value by Row and Column in Excel VBA

⧭ Output:

Run the code. It’ll display the cell value from the 7th row and 3rd column of the used range of Sheet2, which is 78.

Read More: How to Find Value in Column in Excel (4 Methods)


Similar Readings


3. Get Cell Value by Row and Column from a Specific Range in Excel VBA

Finally, we’ll get the cell value by row and column from a selected range of a worksheet.

To get the cell value by row and column from a specific range of a worksheet, you can use the Cells method of VBA, but along the Range object.

For example, to get the value from the cell in the 4th row and the 6th column of the range E2:H14of the worksheet called Sheet3, you can use:

Value = Worksheets("Sheet3").Range("E2:H14").Cells(4, 6)

⧭ Example:

Here we’ve got another worksheet called Sheet3 with two data sets. One with the names and IDs of the students (B2:C14)  of a School, and the other with the names of some students and their marks in Physics, Chemistry, and Mathematics (E2:H14).

Data Set to Get Cell Value by Row and Column in Excel VBA

Now, to get the marks of the 6th student in Chemistry again, you have to get the value from the 7th row and the 3rd column of the range E2:H14 of the worksheet.

The VBA code will be:

⧭ VBA Code:

Sub Cell_Value_from_Selected_Range()

Value = Worksheets("Sheet3").Range("E2:H14").Cells(7, 3)

MsgBox Value

End Sub

VBA Code to Get Cell Value by Row and Column in Excel VBA

⧭ Output:

Run the code. It’ll display the cell value from the 7th row and 3rd column of the range E3:G13 of Sheet3, which is 78.

Read More: How to Find Top 5 Values and Names in Excel (8 Useful Ways)


Things to Remember

Here I’ve used the UsedRange and Range object of VBA in Excel. To know them in detail, you can visit this link.


Conclusion

So, here are the ways to get any cell value by row and column with VBA in Excel. Do you have any questions? Feel free to ask us. And don’t forget to visit our site ExcelDemy for more posts and updates.


Related Articles

Rifat Hassan

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo