Working With Variable-Size Ranges
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.
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.
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.
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
Excel has a long history, and it continues to evolve and change. Consequently, the tips provided here do not necessarily apply to all versions of Excel.
In particular, the user interface for Excel 2007 (and later), is vastly different from its predecessors. Therefore, the menu commands listed in older tips, will not correspond to the Excel 2007 (and later) user interface.
Browse Tips by Category
Search for Tips
Needs tips? Here are two books, with nothing but tips:
Contains more than 200 useful tips and tricks for Excel 2007 | Other Excel 2007 books | Amazon link: John Walkenbach's Favorite Excel 2007 Tips & Tricks