Dutch Gemini's Weblog Pages

February 25, 2009

Walk through a range containing merged cells in Excel VBA

Filed under: Excel,Programming,VBA — dutchgemini @ 12:43 pm
Tags: , ,

To retrieve the range of selected cells on a worksheet for use in VBA there is only way I know: using the Application.Selection property.

The Selection property returns whatever object Excel has currently selected. This can be a single or a range of cells, charts, shapes, and so on. You can inspect the type of object by issuing the TypeName() function. In case of cells, this will be “Range”. The next 2 statements are equivalent (I prefer the second):
  • If TypeName(Selection) <> "Range" Then Exit Sub
  • If Not TypeOf Selection Is Range Then Exit Sub

To walk through a range you can use the For Each … Next construct:

Sub Macro1()
    Dim rRange As Range, rCell As Range

    If Not TypeOf Selection Is Range Then Exit Sub

    Set rRange = Selection
    For Each rCell In rRange
        Debug.Print rCell.Address
    Next rCell
End Sub

However, if the selected range contains merged cells, the above macro will continue to process each cell of the range as if none of the cells were merged. In other words, the loop does not jump to the next cell on the worksheet like when you navigate with the cursor keys.

To make sure that the code also jumps to the next cell, you have to use another approach. It is based on the .Offset method. To keep it simple, the next example only considers a single column and a single area. Multi-column and multi-area selection requires additional coding which was not the scope of this post.

Sub Macro2()
    Dim rRange As Range, rCell As Range

    If Not TypeOf Selection Is Range Then Exit Sub

    Set rRange = Selection
    If rRange.Areas.Count > 1 then Exit Sub
    If rRange.Columns.Count > 1 then Exit Sub

    Set rCell = rRange.Cells(1, 1)        ' Take the first cell in the range
    Do
        Debug.Print rCell.Address
        Set rCell = rCell.Offset(1, 0)    ' Jump 1 row down to the next cell
    Loop Until (rCell.Row > (rRange.Row + rRange.Rows.Count - 1))
End Sub
Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: