VBA Excel – Cells, Ranges and Offset : Cells

Format : Cells(Row,Column)

Excel is represented in a grid format, tabular form in rows and columns, so to reach to particular cell you need tell to compiler the cell is located in which row and which column in that particular row.

Ex: in VBA Excel when you say Cells(2,3) which means, in 2nd row and in 3rd column means “C2”

Some of the very effective functions of Cells are

  • Cells.Select
  • Cells.ClearContents
  • Cells.SpecialCells(xlCellTypeLastCell) (Ex: Delete Blank rows)

These functions are pretty much similar to other VBA Excel functions like UserRange.Clearcontents or UserRange.Select or UsedRange.Copy

To know more about UserRange Click here (VBA Excel-Range)

But while using Cells, sometimes it’s a tedious task to find out which cell you are talking about when you say suppose

Cells(18,31) which means 18th rows and 31st column (“AE”), rather we prefer saying it as “AE18” instead 18,31

So that’s why we have Range and it’s recommended to use Range over Cells at most of the places


2 thoughts on “VBA Excel – Cells, Ranges and Offset : Cells”

  1. Sub GoodTableWithHeaders()

    Dim rTable As Range

    Dim lHeadersRows As Long
    Dim lastline As Double

    Set rTable = Sheets(“Sheet1”).Range(“A1”).CurrentRegion

    lHeadersRows = rTable.ListHeaderRows

    ‘Resize the range minus lHeadersRows rows

    If lHeadersRows > 0 Then

    Set rTable = rTable.Resize(rTable.Rows.Count – lHeadersRows)

    ‘Move new range down to Start at the first data row.

    Set rTable = rTable.Offset(1)
    lastline = Sheets(“Sheet2”).Range(“A1”).End(xlDown).Row + 1

    Sheets(“Sheet2”).Range(“A” & lastline).PasteSpecial xlPasteValues

    End If

    End Sub


Leave a Comment