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
|
Option Explicit
Function xlIsBlank(TargetRange As Range) As Boolean
Dim DataCol As Long
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()
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
|