Show Buttons
Share On Facebook
Share On Twitter
Share On Google Plus
Share On Linkdin
Share On Reddit
Contact us
Hide Buttons

VBA Excel — Cells, Ranges and Offset : Cells

For­mat : Cells(Row,Column)

Excel is rep­re­sented in a grid for­mat, tab­u­lar form in rows and columns, so to reach to par­tic­u­lar cell you need tell to com­piler the cell is located in which row and which col­umn in that par­tic­u­lar row.

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

Some of the very effec­tive func­tions of Cells are

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


These func­tions are pretty much sim­i­lar to other VBA Excel func­tions like UserRange.Clearcontents or UserRange.Select or UsedRange.Copy

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

But while using Cells, some­times it’s a tedious task to find out which cell you are talk­ing about when you say suppose

Cells(18,31) which means 18th rows and 31st col­umn (“AE”), rather we pre­fer say­ing it as “AE18” instead 18,31

So that’s why we have Range and it’s rec­om­mended to use Range over Cells at most of the places

 

You may also like...

2 Responses

  1. harsh says:

    Sub Good­Table­With­Head­ers()

    Dim rTable As Range

    Dim lHead­er­sRows As Long
    Dim last­line As Double

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

    lHead­er­sRows = rTable.ListHeaderRows

    ‘Resize the range minus lHead­er­sRows rows

    If lHead­er­sRows > 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)
    rTable.Copy
    last­line = Sheets(“Sheet2”).Range(“A1”).End(xlDown).Row + 1
    ‘Sheets(“DB”).Range(“A1”).End(xlDown).Select

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

    End If

    End Sub

  1. November 7, 2014

    […] is your friend! There is lot of good doc­u­men­ta­tion, espe­cially on the dif­fer­ent func­tions, 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: