I have Dim a variable as a range.

I am trying to write an IF statement based on that range being empty,
and having no success.

Any help is appreciated

--
SuitedAce
-----------------------------------------------------------------------
SuitedAces's Profile: http://www.excelforum.com/member.php...fo&userid=3584
View this thread: http://www.excelforum.com/showthread.php?threadid=55641

If rng.Value = "" Then

or

If IsEmpty(rng.Value) Then

or

If Len(rng.value) = 0 Then

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"SuitedAces" wrote
in message ...

I have Dim a variable as a range.

I am trying to write an IF statement based on that range being empty,
and having no success.

Any help is appreciated.


--
SuitedAces
------------------------------------------------------------------------
SuitedAces's Profile:
http://www.excelforum.com/member.php...o&userid=35840
View this thread: http://www.excelforum.com/showthread...hreadid=556412

Bob,
This topic interests me, so I'll barge in. This does not seem to
work:

Sub RangeStatus2()
Dim z As Range
Set z = [a1:b5]
If IsEmpty(z.Value) Then
MsgBox "empty"
Else
MsgBox "not empty"
End If
End Sub

What did I do wrong?
James


Bob Phillips wrote:
If rng.Value = "" Then

or

If IsEmpty(rng.Value) Then

or

If Len(rng.value) = 0 Then

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"SuitedAces" wrote
in message ...

I have Dim a variable as a range.

I am trying to write an IF statement based on that range being empty,
and having no success.

Any help is appreciated.


--
SuitedAces
------------------------------------------------------------------------
SuitedAces's Profile:
http://www.excelforum.com/member.php...o&userid=35840
View this thread: http://www.excelforum.com/showthread...hreadid=556412

Zone,

IsEmpty is used to test if a variable is has been initialized. This does
work with a single cell value as this is a simple variant, but not a range
of cells, as this is a 2 dimensional array of values. Even though all of the
elements of the array are empty, the array is not.

You need to test a range differently, either loop through them, or use a
worksheet function

Sub RangeStatus2()
Dim z As Range
Set z = Range("A1:B5")
If Application.CountIf(z, "<") = 0 Then
MsgBox "empty"
Else
MsgBox "not empty"
End If
End Sub


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Zone" wrote in message
oups.com...
Bob,
This topic interests me, so I'll barge in. This does not seem to
work:

Sub RangeStatus2()
Dim z As Range
Set z = [a1:b5]
If IsEmpty(z.Value) Then
MsgBox "empty"
Else
MsgBox "not empty"
End If
End Sub

What did I do wrong?
James


Bob Phillips wrote:
If rng.Value = "" Then

or

If IsEmpty(rng.Value) Then

or

If Len(rng.value) = 0 Then

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"SuitedAces"
wrote
in message
...

I have Dim a variable as a range.

I am trying to write an IF statement based on that range being empty,
and having no success.

Any help is appreciated.


--
SuitedAces

------------------------------------------------------------------------
SuitedAces's Profile:
http://www.excelforum.com/member.php...o&userid=35840
View this thread:
http://www.excelforum.com/showthread...hreadid=556412

The range is multiple cells?

dim myRng as range
if application.counta(myrng) = 0 then
msgbox "all the cells in that range are empty
end if

if myRng is a single cell:
if isempty(myrng.value) then
msgbox "It's empty"
end if

SuitedAces wrote:

I have Dim a variable as a range.

I am trying to write an IF statement based on that range being empty,
and having no success.

Any help is appreciated.

--
SuitedAces
------------------------------------------------------------------------
SuitedAces's Profile: http://www.excelforum.com/member.php...o&userid=35840
View this thread: http://www.excelforum.com/showthread...hreadid=556412

--

Dave Peterson

Suited, Here is another way of doing this. May not be the most
elegant, but it does tell you how many cells aren't empty. James

Sub RangeStatus()
Dim z As Range, c As Range, ct As Long
Set z = [a1:b5] 'set range as needed
ct = 0
For Each c In z
If VarType(c) < vbEmpty Then ct = ct + 1
Next c
MsgBox ct
'if ct<0 then one or more cells is not empty
End Sub



SuitedAces wrote:
I have Dim a variable as a range.

I am trying to write an IF statement based on that range being empty,
and having no success.

Any help is appreciated.


--
SuitedAces
------------------------------------------------------------------------
SuitedAces's Profile: http://www.excelforum.com/member.php...o&userid=35840
View this thread: http://www.excelforum.com/showthread...hreadid=556412

See my later response.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Zone" wrote in message
oups.com...
Suited, Here is another way of doing this. May not be the most
elegant, but it does tell you how many cells aren't empty. James

Sub RangeStatus()
Dim z As Range, c As Range, ct As Long
Set z = [a1:b5] 'set range as needed
ct = 0
For Each c In z
If VarType(c) < vbEmpty Then ct = ct + 1
Next c
MsgBox ct
'if ct<0 then one or more cells is not empty
End Sub



SuitedAces wrote:
I have Dim a variable as a range.

I am trying to write an IF statement based on that range being empty,
and having no success.

Any help is appreciated.


--
SuitedAces
------------------------------------------------------------------------
SuitedAces's Profile:
http://www.excelforum.com/member.php...o&userid=35840
View this thread:
http://www.excelforum.com/showthread...hreadid=556412