Counting selected cells

You can create a macro that works with the selected range of cells. Use the Count property of the Range object to determine how many cells are contained in a range selection (or any range, for that matter). For example, the following statement displays a message box that contains the number of cells in the current selection:

MsgBox Selection.Count Caution

With the larger worksheet size in Excel 2007, the Count property can generate an error. The Count property uses the Long data type, so the largest value that it can store is 2,147,483,647. For example, if the user selects 2,048 complete columns (2,147,483,648 cells), the Count property generates an error.

Fortunately, Microsoft added a new property: CountLarge . CountLarge uses the Double data type, which can handle values up to 1.79+E° 308.

Bottom line? In the vast majority of situations, the Count property will work fine. If there's a chance that you may need to count more cells (such as all cells in a worksheet), use CountLarge instead of Count .

If the active sheet contains a range named data , the following statement assigns the number of cells in the data range to a variable named CellCount :

CellCount = Range("data").Count

You can also determine how many rows or columns are contained in a range. The following expression calculates the number of columns in the currently selected range:

Selection.Columns.Count

And, of course, you can also use the Rows property to determine the number of rows in a range. The following statement counts the number of rows in a range named data and assigns the number to a variable named RowCount :

RowCount = Range("data").Rows.Count

0 0

Post a comment