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
How to Get Cell Value by Row and Column in Excel VBA: 3 Methods
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.
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
⧠Output:
Run the code. It’ll display the cell value from the 7th row and 3rd column of Sheet1, which is 78.
Read More: Excel VBA: Get Cell Value from Another Workbook without Opening
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.
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
⧠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.
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).
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
⧠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.
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.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
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.
Related Articles
- How to Get Cell Value as String Using Excel VBA
- Excel VBA Set Cell Value in Another Worksheet
- Excel VBA: Read Cell Value into Variable
