VBA Express : Excel - Test For Selected Cells Count

Excel

Test For Selected Cells Count

Ease of Use

Easy

Version tested with

2000, 2002

Submitted by:

Justinlabenne

Description:

A function that tests for the number of cells selected in a range.

Discussion:

This function is used to check mainly for an entier sheet selection. When looping through cells based on a users selection, the loop is drastically slowed when processing an entire sheet of cells. This function will return a boolean "True" value when the entire sheet is selected, of if it is not, it will return a Long value with the count of the cells selected. To view an example of it used inside a code, view this kb entry: http://vbaexpress.com/kb/getarticle.php?kb_id=563 The example contains a test for the function and a description of use.

Code:

instructions for use

			Option Explicit 
 
Public Function bCheckCellCount(rSelectedRange As Range) As Variant 
     '   -----------------------------------------------------------------
     '   Function to check if the entire sheet is selected:
     '   Returns:
     '       If all cells (entire sheet) are selected = Boolean (True)
     '       If not all cells = Long (count of cells)
     '   -----------------------------------------------------------------
     
     '   If the selected range of cells = the total # of cells available
     '   then we return a boolean value {True}
     '   because the entire sheet has been determined to be selected
    If rSelectedRange.Cells.Count = Cells.Count Then 
         
        bCheckCellCount = CBool(True) 
         
    Else 
         
         '       If not, return the cell count
        bCheckCellCount = CLng(rSelectedRange.Cells.Count) 
         
    End If 
     
End Function

How to use:

Open an Excel Workbook
Copy the code
Press Alt + F11 to open the Visual Basic Editor (VBE)
Select INSERT > MODULE from the menubar
Paste code into the right pane
Press Alt+Q to return to Excel
Save workbook before any other changes

Test the code:

Call from a sub-procedure
View the example attachment for more info on how this is done, and how the code is incorporated.

Sample File:

TestForCellCount.zip 12.84KB

Approved by mdmackillop

This entry has been viewed 169 times.