Working With Variable-Size Ranges

Category: General VBA | [Item URL]

A companion file is available: Click here to download

In many situations, your VBA procedure needs to work with a range that can vary in size. For example, you may have a worksheet that holds weekly sales data. Every week you add a new row to it. Creating a VBA procedure to manipulate the data can be tricky, since the size of the range will vary from week to week. This document describes a number of VBA techniques that are useful when working with ranges that have an unknown size.

The CurrentRegion Property

If you need to work with an entire range of cells (such as a worksheet database, or list), the CurrentRegion property will be useful. This property returns a range object. To understand how this works, experiment with the Current Region option in the Go To Special dialog box (press F5, then click Special).

The statement below selects the current region for cell A1.

Range("A1").CurrentRegion.Select 

The End Property

You are probably familiar with Excel's keystrokes that let you move or select to the end of a row or column. For example, to move the cell pointer to the last nonblank cell in a column, you press Ctrl+Down Arrow (or, End followed by Down Arrow). To select cells from the active cell down to the first blank cell in the column, you press Ctrl+Shift+Down Arrow (or, End followed by Shift+Down Arrow).

If you record a VBA macro using these keystrokes, you may be surprised that the result isn't what you expect. Rather the record code to simulate these key combinations, the recorder simply records the actual cell addresses. However, VBA contains a useful property (End) that provides the equivalent of this type of action. The statement below selects cells from the active cell down to the last non-empty cell in the column.

Range(ActiveCell, ActiveCell.End(xlDown)).Select 

In this case, xlDown is a built-in constant. As you may expect, there are three other constants to simulate key combinations in the other directions: xlUp, xlToLeft and xlToRight.

VBA Code

Following are VBA procedures that perform a wide variety of cell selections.

Sub SelectDown()
'   Like Ctrl+Shift+Down
    Range(ActiveCell, ActiveCell.End(xlDown)).Select
End Sub

Sub SelectUp()
 '   Like Ctrl+Shift+Up
    Range(ActiveCell, ActiveCell.End(xlUp)).Select
End Sub

Sub SelectToRight() 
 '   Like Ctrl+Shift+Right
    Range(ActiveCell, ActiveCell.End(xlToRight)).Select
End Sub

Sub SelectToLeft() 
 '   Like Ctrl+Shift+Left
    Range(ActiveCell, ActiveCell.End(xlToLeft)).Select
End Sub

Sub SelectCurrentRegion() 
 '   Like Ctrl+Shift+*
    ActiveCell.CurrentRegion.Select
End Sub

Sub SelectActiveArea() 
 '   Like End, Home, Ctrl+Shift+Home
    Range(Range("A1"), ActiveCell.SpecialCells(xlLastCell)).Select
End Sub

Sub SelectActiveColumn() 
 '   Contiguous Cells in ActiveCell's Column 
    If IsEmpty(ActiveCell) Then Exit Sub
'   ignore error if activecell is in Row 1
    On Error Resume Next
    If IsEmpty(ActiveCell.Offset(-1, 0)) Then
        Set TopCell = ActiveCell
    Else
        Set TopCell = ActiveCell.End(xlUp)
    End If
    If IsEmpty(ActiveCell.Offset(1, 0)) Then
        Set BottomCell = ActiveCell
    Else
        Set BottomCell = ActiveCell.End(xlDown)
    End If
    Range(TopCell, BottomCell).Select

End Sub

Sub SelectActiveRow() 
 '   Contiguous Cells in ActiveCell's Row 
    If IsEmpty(ActiveCell) Then Exit Sub
'   ignore error if activecell is in Column A
    On Error Resume Next
    If IsEmpty(ActiveCell.Offset(0, -1)) Then
        Set LeftCell = ActiveCell
    Else
        Set LeftCell = ActiveCell.End(xlToLeft)
    End If
    If IsEmpty(ActiveCell.Offset(0, 1)) Then
        Set RightCell = ActiveCell
    Else
        Set RightCell = ActiveCell.End(xlToRight)
    End If
    Range(LeftCell, RightCell).Select
End Sub

Sub SelectEntireColumn() 
 '   Like Ctrl+Spacebar
    Selection.EntireColumn.Select
End Sub

Sub SelectEntireRow() 
 '   Like Shift+Spacebar
    Selection.EntireRow.Select
End Sub

Sub SelectEntireSheet() 
 '   Like Ctrl+A
    Cells.Select
End Sub

Sub ActivateNextBlankDown() 
 '   Next Blank Cell Below  
     ActiveCell.Offset(1, 0).Select
    Do While Not IsEmpty(ActiveCell)
        ActiveCell.Offset(1, 0).Select
    Loop
End Sub

Sub ActivateNextBlankToRight() 
 '   Next Blank Cell To the Right
    ActiveCell.Offset(0, 1).Select
    Do While Not IsEmpty(ActiveCell)
        ActiveCell.Offset(0, 1).Select
    Loop
End Sub

Sub SelectFirstToLastInRow() 
 '   Select From the First NonBlank to the Last Nonblank in the Row 
    Set LeftCell = Cells(ActiveCell.Row, 1)
    Set RightCell = Cells(ActiveCell.Row, Columns.Count)
    If IsEmpty(LeftCell) Then Set LeftCell = LeftCell.End(xlToRight)
    If IsEmpty(RightCell) Then Set RightCell = RightCell.End(xlToLeft)
    If LeftCell.Column = Columns.Count And RightCell.Column = 1 Then
        ActiveCell.Select
    Else
        Range(LeftCell, RightCell).Select
    End If
End Sub

Sub SelectFirstToLastInColumn() 
 '   Select From the First NonBlank to the Last Nonblank in the Column
    Set TopCell = Cells(1, ActiveCell.Column)
    Set BottomCell = Cells(Rows.Count, ActiveCell.Column)
    If IsEmpty(TopCell) Then Set TopCell = TopCell.End(xlDown)
    If IsEmpty(BottomCell) Then Set BottomCell = BottomCell.End(xlUp)
    If TopCell.Row = Rows.Count And BottomCell.Row = 1 Then
        ActiveCell.Select
    Else
        Range(TopCell, BottomCell).Select
    End If
End Sub