VBA cells object in Excel

In the previous chapters, Range object was discussed.It is simply used to access a contigous range of cells.

The Cells object is used to access a single cell.

The Range object access a cell using its name, like A1,G7 and so on, whereas, Cells object access a cell with the help of Row and Column Index.

The Cells() comes to help, when a row has to be traversed along, with a For loop.

The Range() will be useful to traverse down a column, whereas cells() will be useful for both rows and columns.

The Syntax:


The following example illustrates this:

  1. Sub Cells_demo()
  2. MsgBox "Traversing Row"
  3. For i = 9 To 19
  4. Sheets("Q32").Cells(12, i) = i
  5. Next i
  6. MsgBox "Traversing Columns"
  7. For j = 10 To 19
  8. Sheets("Q32").Cells(j, 9) = j
  9. Next j
  10. MsgBox "That's it"
  11. For i = 9 To 19
  12. Sheets("Q32").Cells(12, i) = ""
  13. Next i
  14. For j = 10 To 19
  15. Sheets("Q32").Cells(j, 9) = ""
  16. Next j
  17. End Sub

As we saw from the demo, The cells method can be used to read or write data along a single row, (L9,M9,N9 and so on).

Whereas with Range, it is not easy to do the same functionality, though, it is more advantageous to read or write along a single column.

The VBA screenshot is as shown below:

excel vba cells object


You can find similar Excel Questions and Answer hereunder

1) How can I enter information in multiple cells simultaneously?

2) I have a complex WorkSheet with lot of fields and data - How can I quickly find the cells that have formulas?

3) How to avoid screen update in Excel VBA. Various application objects explained

4) I have 2 sets of lists from 2 different reports - how can I select the cells with differences?

5) How can I sort data using multiple criteria?

6) How to add email and emailing function in Excel VBA

7) Want to use Microsoft Outlook in Excel. Here some basic explanation to get you started

8) How to lock cells so that no one can change them

9) How can I identify all cells with Conditional Formatting in my WorkSheet?

10) Applying a countif formula only to visible cells in a filtered list in Excel


Here the previous and next chapter