EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home VBA VBA Resources VBA Information Functions VBA Cells
 

VBA Cells

Pradeep S
Article byPradeep S
Madhuri Thakur
Reviewed byMadhuri Thakur

Excel VBA Cells

Excel VBA Cells Function

Cells are one type of element in excel VBA. (Three elements of excel is Workbooks, Worksheets and Ranges/Cells)

 

 

Hierarchical between these elements or object will be:

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

Hireachical Element

Basic Structure For Referring a Cell

Excel VBA allows you to refer to cells in many different ways, cells refers to single cells only. (Note: it can’t refer to multiple cells like Range (“A2:E7”)

It is used for referencing a cell object e.g. it can be written as Cells (6, 5) for referring a cell “F5” where 6 is the column number & 5 is the row number Cells takes row & column number as an argument, cells are located within the range object With the help of cells in VBA, we can perform three important tasks, i.e. we can

  1. Read content from a cell
  2. Write a value to a cell
  3. Change the format of a cell

Difference between range & cells in VBA is Cells usually refer to a single cell at a time, while Range references a group of cells. The cell is a property of range in excel sheet, which is a characteristic, where it is used to describe a range Cells only returns one cell which is used to represent cells within a range of the worksheet.

This cells property is used to specify a single cell or all cells on the worksheet, where it returns a single cell in the Cells collection.

Syntax of VBA Cells in Excel

The syntax for VBA cells function in excel is as follows:

VBA Cells syntax

Cells argument will consider two numeric arguments to represent row & column, where the first one is for representing row number & and the second or last one referring to as column number.

Row Index: Which is row number which we are referring to.

Column Index: Which is column number which we are referring to.

Note: Either numbers or letters can be used to specify the Column index in CELLS E.G. Cells (5, “E”)

Comma (,): Separator between them is the union operator, which is used to combine several range of cells.

Expression = Cells (4, 2) indicates it is “B4” Cell on an active worksheet in row 4 and col 2

OR

Expression = Cells (4, “E”) indicates it is “B4” Cell on an active worksheet in row 4 and col 2

Above either of the code can be used to

Difference between Cell & Range syntax VBA code

To select cell B4 on the active worksheet, you can use either of the following examples:

ActiveSheet.Cells(4, 2).Select

or

ActiveSheet.Range(“B4”).Select

How to Use VBA Cells in Excel?

We will learn how to use VBA cells function with a few examples in excel.

You can download this VBA Cells Excel Template here – VBA Cells Excel Template

Example #1 – VBA Cells

Step 1:  Select or click on Visual Basic in the Code group on the Developer tab or you can directly click on Alt + F11 shortcut key.

VBA Cells Developers

Step 2: To create a blank module, right-click on Microsoft excel objects, in that click on Insert and under the menu section select Module, where the blank module gets created.

VBA Cells Module 1

Step 3: Double click on it, it is also called a code window, where you need to type Sub Cells_Example1() as the first message without any quotes around it. Now, you can observe, Excel automatically adds the line End Sub below the first message line when you press Enter.

Code:

Sub Cells_Example1()

End Sub

VBA Cells Example 1.1

Step 4: Now, all the codes which you enter must be between these two lines, Now you can start typing CELLS VBA code or syntax. Before typing code, enter CTRL + SPACE where VBA Intellisense Drop-down Menu appears, which helps you out in writing the code & you can autocomplete words in the VB editor.

The dropdown contains a list of all the members of the VB OBJECT MODEL active reference (i.e. includes objects, properties, variables, methods, and constants). This feature helps out in saving time & prevent misspell the words or typo-error.

VBA Cells Example 1.2

Step 5: After typing cells, click on the tab key to select it.

Code:

Sub Cells_Example1()

Cells

End Sub

Example 1.3

Step 6: Once you leave a space and enter open bracket “(”, CELLS argument will appear where it considers two numeric arguments to represent row & column index, where the first one is for representing row number & and the second or last one referring to as column number.

Code:

Sub Cells_Example1()

Cells(

End Sub

VBA Cells Example 1.4

Step 7: Now, I enter the row index number as “4” & column index as “2” and close the bracket. Full stop and enter one space by clicking on the spacebar, now the cells argument is ready. And I want value in it. So, I enter again enter CTRL + SPACE where VBA Intellisense Drop-down Menu appears, type “Val” which means value.

Code:

Sub Cells_Example1()

Cells(4, 2). Val

End Sub

Example 1.5

Step 8: Suppose you want “HI” to appear in that cell. For that, you need to type = “HI”. and click enter.

Code:

Sub Cells_Example1()

Cells(4, 2).Value = "Hi"

End Sub

Example 1.6

Step 9: Now, the code is ready, you can run the macro by clicking the Run Sub button (i.e. green “play” button) or by pressing F5. You can observe “Hi” appears in the cell “B4”

VBA Cells

Things to Remember

  • Only one cell can be referred at a time with the help of Cells property.
  • Cell property is very significant & useful in a programming loop.
  • If you missed out or don’t specify a worksheet (by its name in VBA code), Excel considers or assumes the ACTIVE Sheet in a workbook.
  • If you missed out or don’t specify row and column index argument, then Excel will refer to all cells on the worksheet. E.g. Cells is the syntax for all cells on the Active Sheet.
  • You can combine cells with range together to define a starting & ending pint of a range with variables.

Recommended Articles

This is a guide to VBA Cells. Here we discuss how to use Excel VBA Cells Function along with practical examples and downloadable excel template. You can also go through our other suggested articles –

  1. VBA Web Scraping
  2. VBA XML
  3. VBA Subscript out of Range
  4. VBA IsError

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Web Development & many more.

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

Download VBA Cells Excel Template

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW