September 14, 2008

Referring to Rows & Columns

Filed under: VBA — fatrunner @ 9:53 pm
Tags: , ,

In the previous post I spoke about referring to cells and ranges of cells, but in the instance you need to refer to an entire row or an entire column you can do so with what was learned previously, but it would be rather cumbersome. Visual Basic has a way for us to so simply. The format is just like what we used when referring to a range, but instead of the word Range we’ll use the word Rows or Columns. A couple of examples are:

Rows(1)  'refers to row 1
Columns(1)  'refers to column 1 or you can use...
Columns("A")  'again refers to the 1st column and has to have the quotes
Columns  'no numbers or letters refers to ALL columns on the worksheet

You can also select multiple rows or columns by separating with commas like the following examples:

Rows("1:1,3:3")  'refers to multiple non-contiguous rows, i.e. row 1 AND row 3
Rows("1:3")  'refers to multiple contiguous rows, i.e. rows 1 THROUGH 3

In my next post I’ll show you a shortcut notation for referring to cells.

September 13, 2008

Referring to Cells and Ranges of Cells

Filed under: VBA — fatrunner @ 2:06 pm
Tags: , ,

Now that you’ve got a workbook open, it is very likely that one of the first things you’ll need to do is to refer to a cell or a range of cells in order to put data there or to manipulate the data that is already there. It takes one simple line of code to do so and the format is as follows: Range(“cell range“). Following is a number of variations to select a number of different ranges.

Range("A1")  'select cell A1
Range("A1:B2")   'select cells from A1 through B2
Range("A1:B2,A3:B3")  'select a non-contiguous range of cells 
Range("A:A")  'select column A
Range("1:1")   'select row 1

The hardest part for me to remember is the quotation marks. It is very similar to using a ‘sum’ formula in the way you present the cells in the parenthesis…just remember the quotes. This is the most common way you will see a range of cells or a single cell referred to when looking at VBA code. This is called the A1 notation obviously because it refers to cells using the letter (for the column) and number (for the row).

Sometimes it makes sense to refer to cells in a different way using index numbers. What that means is using numbers to refer to both the column and the row.  The confusing part for me is that the notation gets reversed from what you are used to when using the A1 notation. A1 notation starts with the letter, i.e. the column, and then the number, i.e. the row. Using the index number reverses that, referring to the row first and then the column. Other than only seeing numbers to clue you in that the index numbers are being used as opposed to A1 notation, the word ‘range’ is also not used. Instead the word ‘cell’ is used.

The reason for this type of notation is that it is useful when using a counter or other method, such as looping to increment the cell location you are referring to. Below is an example used in Excel’s help file to show the use of the Index Notation:

Worksheets("Sheet1").Cells(6,1).Value = 10

This line of code tells Excel to find the cell that is in the 6th row and 1st column (Cell A6 using A1 notation)on Sheet1 and make its value equal to 10. Excel’s help file goes on to show an example of how this would be used if it were looped by showing the following code:

Sub CycleThrough()
    Dim Counter As Integer
    For Counter = 1 To 20
        Worksheets("Sheet1").Cells(Counter, 3).Value = Counter
    Next Counter
End Sub

If you were going through the help file this would show you a number of things that would be completely new to you that wouldn’t make any sense but I think it is simple enough that you could get the jist of how it would be used.  Since I’ve put the code out there I will explain the steps that this code is running through in English.

Sub CycleThrough() is the opening of the VBA code. All code starts with the word Sub and then the name of the macro followed with the open and closed partenthesis.

Dim Counter As Integer. This tells Excel to create a variable called Counter and make that variable an integer.

For Counter = 1 To 20. This starts a For Next loop and tells Excel that for every time you go through this loop find the variable named Counter and assign it the value of 1 until you get to the ‘Next’ line. Then use the value of 2, then 3, then 4 and so on until 20 is reached.

Worksheets(“Sheet1”).Cells(Counter, 3).Value = Counter.  This is where the value of the Counter plays its part. The first time through the loop Excel has assigned the value of 1 to the variable Counter.  In essence you can invision this line of code rewritten with the number 1 in the place of the word Counter.  The first time through this loop Excel will find the cell that is in the first row down and third column over (this would be cell C1) on Sheet1 and make it value equal to 1, which is the value currently being held for the variable Counter. The next time the loop runs through it will move to cell C2, the 2nd row down and 3rd column over, and make its value equal to 2.

Next Counter. This moves the code back up to the For Counter line and increments the number up by one.

End Sub. This ends the code.

That gets off the topic a bit, but it does show how and why you would use this variation of notation to refer to a cell.

Blog at WordPress.com.