Understand range areas in Excel VBA


[range areas]

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.


[range areas]

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.

' Loop through range's areas.
sheet.Cells(1, col3).Value = "By Areas"
i = 2
For Each area In rng.Areas
    sheet.Cells(i, col3).Value = "Area " & area.Address
    i = i + 1
    num_values = area.Cells.Count
    For j = 1 To num_values
        sheet.Cells(i, col3).Value = "    " & _
            area.Cells(j).Address & ": " & area.Cells(j).Value
        i = i + 1
    Next j
Next area>

This code loops through the range’s Areas collection. Each area is a simple Range so you can loop through it by using the simpler For i loop if you like.

Summary

The most important thing to take away from this example is the fact that you can’t use a simple For i loop to loop over the items in a range that includes multiple range areas. You can use a For Each loop or you can loop through the range’s areas, particularly if you want to handle each area separately.

In my next post I’ll show how you can modify my earlier example that randomizes items in an Excel worksheet so it can handle multi-area ranges.


Download Example   Follow me on Twitter   RSS feed   Donate




About RodStephens

Rod Stephens is a software consultant and author who has written more than 30 books and 250 magazine articles covering C#, Visual Basic, Visual Basic for Applications, Delphi, and Java.
This entry was posted in Excel, VBA and tagged , , , , , , , , , . Bookmark the permalink.

1 Response to Understand range areas in Excel VBA

  1. Riaan says:

    Thanx Rod i really appreciate your help.

    Regards
    riaan

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.