Excel

Avoid Unnecessary Loops through cells

Ease of Use

Intermediate

Version tested with

2002 

Submitted by:

ALe

Description:

Function to speed up loops avoiding to consider blank cells. The function has optional values (exclude cells whose content is a text or a number) and it is customizable regarding your need. 

Discussion:

The fact is that most of the times we need to do loop through cells in order to find a value or a text. In many projects I actually don't need to loop cell by cell but I just need to check only the cells that are not empty, or sometimes the cells that are only values, or cells that are only string. With this function the loop will consider just this kind of cells, avoiding blank cells and, therefore, avoiding useless loops and saving time. 

Code:

instructions for use 

			
Option Explicit 
 'As you can see function can be modified with different optional values (only logicals,
 'only errors, only constants, only formulas, etc...)
 
Function CoolRange(MyRange As Range, Optional NoNumbers As Boolean, Optional NoTexts As Boolean) As Range 
     
    Dim RgConstants As Range, RgFormulas As Range 'Ranges to contain cells with constants and formulas
    Dim MyOption As Integer 'string to consider optional values
     
    If NoNumbers = False Then MyOption = 1 'including cells that contain numbers
    If NoTexts = False Then MyOption = MyOption + 2 'including cells that contain text
     
    MyOption = MyOption + 20 'get the value for XlSpecialCellsValue, argument of SpecialCells
     'regarding the optional values
     
    On Error Resume Next 'in case there are no constants or formulas in the range
    Set RgConstants = MyRange.SpecialCells(xlCellTypeConstants, MyOption) 'get constants cells
    Set RgFormulas = MyRange.SpecialCells(xlCellTypeFormulas, MyOption) 'get formulas cells
     
    On Error Goto 0 
    Select Case True 
    Case RgConstants Is Nothing And RgFormulas Is Nothing: Exit Function 'range is empty
    Case RgConstants Is Nothing: Set CoolRange = RgFormulas 'no constants found
    Case RgFormulas Is Nothing: Set CoolRange = RgConstants 'no formulas found
    Case Else: Set CoolRange = Union(RgConstants, RgFormulas) 'GET THE CELLS NOT BLANKS
    End Select 
     
     'clear
    Set RgConstants = Nothing 
    Set RgFormulas = Nothing 
End Function

How to use:

  1. Open an Excel Workbook
  2. Press Alt + F11 to open the Visual Basic Editor (VBE)
  3. Select INSERT > MODULE from the menubar
  4. Paste code into the right pane
  5. Press Alt+Q to return to Excel
  6. Save workbook before any other changes
  8. Create a routine using the CoolRange function or just see the routines in the attached file
 

Test the code:

  1. Open the attached file "Avoid Unnecessary Loops"
  2. Enable macros
  3. Click on the test button to see the function at work
 

Sample File:

Avoid Unnecessary Loops.zip 21.12KB 

Approved by mdmackillop


This entry has been viewed 297 times.

Please read our Legal Information and Privacy Policy
Copyright? 2004 - 2005 VBA Express