Title: Understand range areas in Excel VBA

Most of my VBA examples assume that the current selection is a single block of cells. If the selection includes a single area, then you can easily loop through the cells that it contains.

Unfortunately if the selection includes multiple range areas, then trying to loop through the selection's cells may give strange results where cells seem to not be part of the selection.

This example shows three ways that you can loop through a selection's cells. When you click the Show Values button, the example loops through the current selection's cells in three ways. The following code shows the outline of the macro that the button executes.

Sub ForLoop() Dim col1 As Integer Dim col2 As Integer Dim col3 As Integer Dim rng As Range Dim cell As Range Dim area As Range Dim sheet As Worksheet Dim num_values As Integer Dim i As Integer Dim j As Integer ' Display headers. Set sheet = Application.Sheets(1) Set rng = Application.Selection col1 = 4 col2 = 5 col3 = 6 For i = col1 To col3 sheet.Columns(i).ClearContents Next i sheet.Columns(col1).Interior.Color = RGB(255, 182, 193) sheet.Columns(col2).Interior.Color = RGB(144, 238, 144) sheet.Columns(col3).Interior.Color = RGB(173, 216, 230) ... End Sub

This code declares a bunch of variables. It then sets variables sheet and rng equal to the workbook's first worksheet and current selection so they are easier to use.

Next the code sets variables col1, col2, and col3 to the columns where it should place output. It loops through those columns clearing any previous results and then gives those columns pink, light green, and light blue backgrounds.

The previous code snippet omits the looping code, which it the part that's really interesting. The following code shows the next piece, which loops through the range's cells in the most obvious but incorrect way.

' Loop through range indexes. sheet.Cells(1, col1).Value = "For i" num_values = rng.Count For i = 1 To num_values sheet.Cells(i + 1, col1).Value = rng(i).Address + ": " + rng(i).Value Next i

This code sets num_values to the number of cells in the range and then makes variable i loop through the values 1 to num_values. Inside the loop, the code uses i as an index into the range and displays that cell's address and value.

If the range includes a single area, then this works. For example, the following picture shows the results when the selection includes only cells A5 through A7.

You can see in columns D, E, and F that all three methods find the same cells.

However, if the selection includes multiple range areas, then this technique of accessing the range's cells by index doesn't work. In that case the indexes are in relation to the selection's first area. After the index passes the end of that area, the other accessed cells just drop off the end off that area. Look at the

The following code shows a second method for accessing the range's cells.

' Loop through the range's cells. sheet.Cells(1, col2).Value = "For Each" i = 2 For Each cell In rng sheet.Cells(i, col2).Value = cell.Address + ": " + cell.Value i = i + 1 Next cell

This code uses a For Each loop to loop through the range. Each of the loop's items is a Range representing one of the range's cells. Using an index as in rng(i) doesn't work with multiple range areas, but this loop does. That's somewhat counterintuitive because both seem to be accessing the range's default Item property.

Anyway, you can see that this version works in the light green column in the picture at the top of the post.

The main drawback to this method is that you cannot access an arbitrary cell in the range. For example, this method won't let you easily change the value of the range's fourth cell. If you need that kin of access, loop through the cells and copy references to the cells into an array. Then you can access the cells through the array.

The following code shows the last method that this example uses to access the range's cells.

This code uses a For Each loop to loop through the range. Each of the loop's items is a Range representing one of the range's cells. Using an index as in rng(i) doesn't work with multiple range areas, but this loop does. That's somewhat counterintuitive because both seem to be accessing the range's default Item property.

Anyway, you can see that this version works in the light green column in the picture at the top of the post.

The main drawback to this method is that you cannot access an arbitrary cell in the range. For example, this method won't let you easily change the value of the range's fourth cell. If you need that kin of access, loop through the cells and copy references to the cells into an array. Then you can access the cells through the array.

The following code shows the last method that this example uses to access the range's cells.

Download the example to experiment with it and to see additional details.