Need help with an if statement

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" <SuitedAces.2a40b5_1151493324.046@excelforum-nospam.com> wrote
> in message news:SuitedAces.2a40b5_1151493324.046@excelforum-nospam.com...
> >
> > 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" <jkend69315@aol.com> wrote in message
news:1151497234.295372.106810@d56g2000cwd.googlegroups.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" <SuitedAces.2a40b5_1151493324.046@excelforum-nospam.com>
wrote
> > in message
news:SuitedAces.2a40b5_1151493324.046@excelforum-nospam.com...
> > >
> > > 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
> > >
>