Using IsBlank in VBA

[Conan Kelly]

Hello all,

Can I use the IsBlank worksheet function in VBA? It doesn't show up in the Application.WorksheetFunction list.

Is it part of the Analysis ToolPak? I have references in my project to "funcres" & "atpvbaen.xls" and it is still not showing up in
the list.

How do I use it, if I can?

Thanks for any help anyone can provide,

Conan Kelly

 

[Ikaabod]

Did you try isempty()?

 

[Zack Barresse]

There are some different native functions for using VBA. There is IsEmpty,
IsMissing, IsNull, Is Nothing (space intended, argument), IsDate, IsNumeric,
IsArray, IsObject, IsArray, etc. The Help files have them listed fairly
well.

If you're trying to find if a cell is blank, you can always use the Len
function ..

If Len(Yourcell.value) = 0 then
msgbox "blank"
else
msgbox "not blank"
end if

HTH

 

[Zack Barresse]

Btw, (posted too quickly) there are some worksheet functions that are not
available to the VBE via App.WorksheetFunction.., just Fyi.

 

[Ikaabod]

I just realized that you might have been asking if the entire worksheet
is empty... one way to check is the following code:

Sub test()
If ActiveSheet.UsedRange.Cells.Address = "$A$1" And
ActiveSheet.Range("A1").Value = "" Then
MsgBox "Clear"
Else
MsgBox "Not Clear"
End If
End Sub

 

[Zack Barresse]

If that's the case, just use ..

If WorksheetFunction.CountA(Cells) = 0 Then
msgbox "empty"
Else
msgbox "has data"
End If

The way you've shown, data can be in A1 and give a false positive.

HTH