Description:

Its one of my first questions when I started learning EXcel VBA, How to Read or Get Data from Worksheet Cell to VBA? To automate any thing in excel, first we need to read the data in Worksheet and based on that we can execute the next steps.

Read or Get Data from Worksheet Cell to VBA in Excel – Solution(s):

It is very simple to read the data from Excel to VBA. We can use Cell or Range Object to refer a Worksheet Cell.

Get Data from Worksheet Cell – An Example of using Cell Object

The following example will show you how to read or get the data from Worksheet Cell using Cell Object.

Example Codes

In this example I am reading the data from first Cell of the worksheet.

Sub sbGetCellData()

MsgBox Cells(1, 1)
'Here the first value is Row Value and the second one is column value 
'Cells(1, 1) means first row first column
End Sub

In this example I am reading the data from first row and fourth column of the worksheet.

Sub sbGetCellData1()

MsgBox Cells(1, 4)

End Sub

Here is sample picture, which helps you to understand this concepts. Any Row or Column number start with 1 and You have to specify Cell(RowNumber,ColumnNumber) to read the data from a Cell of the Worksheet.

Reading data from Cells

Get Data from Worksheet Cell – An Example of using Range Object

The following example will show you how to read or get the data from Worksheet Cell or Range using Range Object.

Example Codes

In this example I am reading the data from first Cell of the worksheet.

Sub sbGetCellData2()

MsgBox Range("A1")
'Here you have to specify the Cell Name which you want to read - A is the Column and 1 is the Row

End Sub

Here is sample picture, which helps you to understand this concepts. If you select any cell in the worksheet, you can see the name of that cell in the Name Box.

Reading data from Cell Range

Get Data from Worksheet Cell – Specifying the Parent Objects

When you are reading using Cell or Range object, it will read the data from Active Sheet. If you want to read the data from another sheet, you have to mention the sheet name while reading the data.

The below example is reading the data from Range A5 of Sheet2:

Sub sbGetCellData2()

MsgBox Sheets("Sheet2").Range("A5")
'Here the left side part is sheets to refer and the right side part is the range to read.

End Sub

In the same way you can mention the workbook name, if you are reading the data from different workbooks.

Premium Project Management Templates

50+ Professional Project Management Templates!

A Powerful & Multi-purpose Templates for project management. Now seamlessly manage your projects, tasks, meetings, presentations, teams, customers, stakeholders and time. This page describes all the amazing new features and options that come with our premium templates.

PREMIUM TEMPLATES
LIMITED TIME OFFER
ON SALE80% OFF
BROWSE ALL TEMPLATES

50+ Project Management Templates Pack

Excel PowerPoint Word

VIEW DETAILS

Ultimate Project Management Template – Advanced

Excel Template

VIEW DETAILS

ULTIMATE PROJECT MANAGEMENT TEMPLATE

Excel Template

VIEW DETAILS

20+ Excel Project Management Pack

Excel Templates

VIEW DETAILS

20+ PowerPoint Project Management Pack

PowerPoint Templates

VIEW DETAILS

10+ MS Word Project Management Pack

Word Templates

VIEW DETAILS

By Published On: May 10th, 2013Categories: Excel VBATags:

Share This Story, Choose Your Platform!

About the Author: Valli

Excel VBA Developer having around 8 years of experience in using Excel and VBA for automating the daily tasks, reports generation and dashboards preparation. Valli is sharing to helps us automating daily tasks.

70 Comments

  1. Magesh February 18, 2014 at 3:02 PM

    I want continue number from A1 to A10 but only while am typed in B1 to B10 like
    A1 1 Magesh
    A2 2 Kumar
    A3 3 Sathish
    A4
    A5 4 Raja
    A6 5 Ram
    A7 6 Raju
    A8 7 Samy

    A9 8 Prabu
    A10 9 Raja

    A11 10 Magesh

    how to put this number alternately in Ms Excel 2007 using VBA.

    with regards,
    Magesh

    • PNRao February 18, 2014 at 11:27 PM

      Hi Magesh,

      You can use the following code. Paste this code in your worksheet module:


      Private Sub Worksheet_Change(ByVal Target As Range)
      'print if B is not empty and A is empty
      If Cells(Target.Row, 2) <> "" And Cells(Target.Row, 1) = "" Then
      Cells(Target.Row, 1) = Target.Row
      End If

      End Sub

      Thanks-PNRao!

    • Ahmed March 20, 2016 at 1:47 PM

      i have excel sheet need to create sub menus , when select value its creates sub menu , when select value its generate another menu with formulas & conditions