VBA Express : Excel - Test a range for any kind of data

Excel

Test a range for any kind of data

Ease of Use

Easy

Version tested with

2000

Submitted by:

MWE

Description:

Tests a target range for any data, returns TRUE or FALSE

Discussion:

VB/VBA have many useful ?is? functions, e.g., IsArray, IsDate, IsEmpty, IsError, IsNumeric, IsNull, etc. If one wishes to test an Excel range for the presence of any data, IsEmpty and IsNull will not work properly because the former is looking for ?not initialized? and the latter is looking for true ?nulls?. xlIsBlank examines a range for any data and returns True if no data is found and False if data is found in any cell

Code:

instructions for use

			Option Explicit 
 
Function xlIsBlank(TargetRange As Range) As Boolean 
     '
     '****************************************************************************************
     '       Title       xlIsBlank
     '       Target Application:  MS Excel
     '       Function    tests for any data in the target range
     '           if no data found, then xlIsBlank = True
     '           if data found in any cell in the range, then xlIsBLank = False
     '       Limitations:    NONE
     '       Passed Values:  TargetRange
     '
     '****************************************************************************************
     '
     '
    Dim DataCol As Long 
     
     '
     '           test the first cell and, if blank, then test all remaining cells
     '             if any have data, set xlIsBlank to False, else set to True
     '
    With TargetRange 
        If Trim(.Cells(1)) <> "" Then 
            xlIsBlank = False 
            Exit Function 
        End If 
        On Error Resume Next 
        DataCol = .Cells.Find("*", .Cells(1), xlFormulas, _ 
        xlWhole, xlByColumns, xlPrevious).Column 
        If Err <> 0 Then DataCol = 0 
    End With 
    If DataCol = 0 Then 
        xlIsBlank = True 
    Else 
        xlIsBlank = False 
    End If 
     
End Function 
 
Sub xlIsBlank_Test() 
     '
     '****************************************************************************************
     '       Title       xlIsBlank_Test
     '       Target Application:  MS Excel
     '       Function    demos xlIsBlank function
     '       Limitations:    NONE
     '       Passed Values:  None
     '
     '****************************************************************************************
     '
     '
     
    MsgBox "return from IsEmpty for the current selection is " & _ 
    vbCrLf & vbTab & vbTab & IsEmpty(Selection) & vbCrLf & vbCrLf & _ 
    "return from IsNull for the current selection is " & _ 
    vbCrLf & vbTab & vbTab & IsNull(Selection) & vbCrLf & vbCrLf & _ 
    "return from xlIsBlank for the current selection is " & _ 
    vbCrLf & vbTab & vbTab & xlIsBlank(Selection) 
     
End Sub

How to use:

Copy the above code.
Open any workbook.
Press Alt + F11 to open the Visual Basic Editor (VBE).
In the left side window, select the target spreadsheet [it will likely be called VBAProject(name.xls) where name is the name of the spreadsheet]
Select an existing code module for the target worksheet; or from the Insert Menu, choose Insert | Module.
Paste the code into the right-hand code window.
Close the VBE, save the file if desired.
See ?Test The Code? below

Test the code:

Open the example
Select some cells (with or without date)
Click the command button

Sample File:

xlIsBlank.zip 20.79KB

Approved by mdmackillop

This entry has been viewed 171 times.