Information
The topic you requested is included in another documentation set. For convenience, it's displayed below. Choose Switch to see the topic in its original location.

Select a Column or Row of Cells in a Used Range Quickly

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

Select a Column or Row of Cells in a Used Range Quickly

by Mike D. Jones
Application: Excel 2000
Operating system: Windows
download
ftp.elementkjournals.com/o2k/Feb01/SelectColumns.xls

One of the greatest advantages of Visual Basic for Applications (VBA) is its ability to modify existing features and make them even more powerful. A great example of this is using the [Ctrl] key with any arrow key in Excel. When you use this technique, it lets you select the first or last cell of a data region (a block of adjacent data in a column or row) by holding down [Ctrl] and then pressing an arrow key. If you press [Ctrl][], Excel selects the next cell upward in the column that begins or ends a data region. Pressing [Ctrl][] selects the next cell downward in the column that begins or ends a data region. The same goes for pressing [Ctrl][] or [Ctrl][] to move around in the current row. In addition, if you hold down [Shift][Ctrl] and press an arrow key, Excel highlights all cells from the active cell to the end of the data region. Unfortunately, this feature only lets you move in one direction; if the currently selected cell is in the middle of a column, you can select up or down, but not the entire column. It would be more useful if we could select an entire column or row of data cells, no matter where the active cell happened to be. In this article, we'll create a macro to modify this technique to do just that.

Plan the macro

In a nutshell, here's what we want our macro to do:

  • Find the top data cell of the current column.
  • Find the bottom data cell of the current column.
  • Select the column from top to bottom.

To help us accomplish this, we'll use VBA's End method, which is similar to the [Ctrl] key with any arrow key combination in functionality. It takes the simple format

range.End(direction)

The range is any cell or other source range where the command starts. If you were doing this manually, it would be the active cell. The direction argument is one of the following four VBA constants, similar to the arrow keys: xlUp, xlDown, xlLeft and xlRight.

If you've used the keyboard shortcut for this feature, however, you know that it has one minor flaw. If, say, you're in the last data cell of a column and you press [Ctrl][], Excel moves to the very bottom of the spreadsheet--row 65,536! And it does so even though the cell is blank. While this is only a minor inconvenience, we certainly don't want our macro to select a column of blank cells. To avoid this potential glitch, we'll first test to see if the active cell is at the very top or bottom of the data range.

So, with that in mind, we need the following steps in our macro:

  • If the insertion point is at the very top of the data range, mark it as the upper-bound cell.
  • Otherwise, use [End] to find the top cell in the data range.
  • If the insertion point is at the very bottom of the data range, mark it as the lower-bound cell.
  • Otherwise, use [End] to find the bottom cell in the data range.
  • Finally, select the data range from top to bottom.

Create the macro

To create the macro, select Tools | Macro | Macros from Excel's menu bar or press [Alt][F8]. Type SelectColumn in the Macro Name text box, and then click Create. The Visual Basic Editor (VBE) opens the Module window and creates a new module. Enter the code in Listing A in the Module window exactly as shown.

Listing A: The SelectColumn code

Sub SelectColumn()
Dim UpBound As Range
Dim LowBound As Range

If ActiveCell.Row > 1 Then
	If IsEmpty(ActiveCell.Offset(-1, 0)) Then
  	Set UpBound = ActiveCell
	Else
  	Set UpBound = ActiveCell.End(xlUp)
	End If
Else
	Set UpBound = ActiveCell
End If

If ActiveCell.Row < Rows.Count Then
	If IsEmpty(ActiveCell.Offset(1, 0)) Then
  	Set LowBound = ActiveCell
	Else
	Set LowBound = ActiveCell.End(xlDown)
	End If
Else
	Set LowBound = ActiveCell
End If

Range(UpBound, LowBound).Select

Set UpBound = Nothing
Set LowBound = Nothing

End Sub

To cut down on keystrokes, let's attach the macro to the workbook so that it runs whenever we double-click on a cell. To do this, in the Project Explorer window, double-click on ThisWorkbook in the Microsoft Excel Objects folder. (If the Project Explorer window isn't currently displayed, press [Ctrl]R.) The VBE displays the workbook's General Declarations section in the Module window. Using Figure A as a guide, select the following options in the VBE window: from the Object dropdown list in the upper-left corner of the Module window, select Workbook; then choose SheetBeforeDoubleClick from the Procedure dropdown list at the right. Between the Sub and End Sub statements, enter

Call SelectColumn
Figure A: This procedure tells Excel to run the SelectColumn macro whenever you double-click on a cell.
[ Figure A ]

Test the macro

Switch back to the spreadsheet and enter a column of values, then double-click on any cell in the column. This action triggers the SheetBeforeDoubleClick procedure, which runs the SelectColumn macro. The macro declares range variables to mark the first and last cells in the data range. Notice that we used the IsEmpty() function to check if the cells above--.Offset(-1,0)--and below--.Offset(1,0)--the active cell are blank. After determining the upper- and lower-bound cells based on the results of that test, the macro selects the range using UpBound and LowBound as the starting and ending points. The macro also contains two conditional statements to see if the active cell is in the first or last row of the worksheet. Otherwise, you'll receive a runtime error. For example, when the top row contains the ActiveCell, there isn't any cell above the first row. Left unchecked, this causes an error with the expression ActiveCell.Offset(-1,0) because the macro still tries to determine if this nonexistent cell is empty.

Notes

Sometimes, when you double-click on a cell, the entire column might not be selected. Instead, the active cell moves to the top or bottom of the data range. This happens if you double-click on a line between cells and not on the cell itself.

Also, while we used this function to select a column, you can easily modify it to select a row. To do so, change the direction of the constants used with the End methods to xlToLeft and xlToRight. Also, alter the arguments in the Offset property to

Offset(0,-1)

to test the left-bound cell, and

Offset(0,1)

to test the right-bound cell. Finally, change the Row and Rows properties in the conditional statements to Column and Columns.

Conclusion

One of the advantages of using VBA is the added control it gives you to improve upon an application's built-in features. In this article, we've shown you how to use the End method to select an entire column of used cells.

Many thanks to Dennis Kurnot for contributing to this macro.

Copyright © 2001 Element K Content LLC. All rights reserved. Reproduction in whole or in part in any form or medium without express written permission of Element K Content LLC is prohibited. Element K is a service mark of Element K LLC.

Show: