Cells

To reach to a particular cell you need to tell to compiler the cell is located in which row and which column.

Example: in VBA Excel when you say Cells(2,1) which means, in 2nd row and in 1st column means “A2”

I strongly recommend that you use Range instead of Cells to work with cells and groups of cells. It makes your sentences much clearer and you are not forced to remember that column AD is column 30.

The only time that you will use Cells is when you want to select all the cells of a worksheet. For example:

Cells.Select

To select all cells and then empty all cells of values or formula you will use:

Cells.ClearContents

Range

A range refers to a cell or a range of cells. A range can be of three types:

One cell (Single Cell)

Multiple cells

Multiple non-contiguous cells – the same as using with CTRL + Clicking cells

To select a single cell you will write:

Range(“A1”).Select

To select a set of contiguous cells you will use the colon and write:

Range(“A1:G5”).Select

To select a set of non contiguous cells you will use the comma and write:

Range(“A1,A5,B4”).Select

To select a set of non contiguous cells and a range you will use both the colon and the comma:

Range(“A1,A5,B4:B8”).Select

Offset

The Offset property is the one that you will use the most with Range to move around the sheet.

To move one cell down (from B2 to B3):

Range(“B2”).Offset(1,0).Select

To move one cell up  (from B2 to B1):

Range(“B2”).Offset(-1,0).Select

To move one cell to the left  (from B2 to A2):

Range(“B2”).Offset(0,-1).Select

To move one cell down from the selected cell:

ActiveCell.Offset(1,0).Select

As you notice the first argument between the parentheses for Offset is the number of rows and the second one is the number of columns. So to move from A1 to G6 you will need:

Range(“A1”).Offset(5,6).Select

 

 

author image

About admin

You Might Also Like...

Team - National Skills Foundation of India
Retrieving Data from Mysql/Mariadb
Message Box in VBA

5 Comments

  1. My spouse and I stumbled over here by a different page and thought I might check things
    out. I like what I see so now i am following
    you. Look forward to finding out about your web page again.

  2. Nice blog here! Additionally your website so much up very fast!
    What host are you using? Can I am getting your affiliate hyperlink to your host?
    I desire my web site loaded up as fast as yours lol

  3. I all the time emailed thiss web site post page to all my associates, since if like to read it after that my links will
    too.

  4. Hi there, yeah this paragraph is in fact good and I have learned lot of things from it concerning blogging.

    thanks.

  5. It’s an awesome post designed for all the web users; they will take advantage from
    it I am sure.

Leave a Reply

Your email address will not be published.

Enjoy the tutorial? Please spread the word :)

Follow by Email
Facebook
Facebook
Google+2k
Google+
http://hirdeshbhardwaj.com/2017/05/14/vba-excel-cells-ranges-offset
YouTube74
YouTube