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


You may also like...

2 Responses

  1. harsh says:

    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

  1. November 7, 2014

    […] is your friend! There is lot of good documentation, especially on the different functions, i.e. http://excel-macro.tutorialhorizon.c…-offset-cells/ Last edited by Joe4; Today at 09:13 […]

Leave a Reply

Your email address will not be published. Required fields are marked *

%d bloggers like this: