Need help with an if statement

[SuitedAces]

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.

[Bob Phillips]

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]

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
> >

[Bob Phillips]

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
> > >
>

[Dave Peterson]

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

[Zone]

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

[Bob Phillips]

See my later response.

--
HTH

Bob Phillips

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

"Zone" <jkend69315@aol.com> wrote in message
news:1151499736.499607.297110@b68g2000cwa.googlegroups.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
>

[SuitedAces]

Thank you for your replies , here is my code
Here is what I have tried so far and here are the results I have gotten


**********************************************************
Private Sub UnhideHide(x As Integer)
Dim CrosstableCorner As Range
Set CrosstableCorner = Range("Crosstable_Corner")

x = x - 1

Dim TeamsThirdRow As Range
Dim TeamsFourthRow As Range
Dim BothTeams As Range
Set TeamsThirdRow = Range(CrosstableCorner.Offset(x * 4 + 3, 1), CrosstableCorner.Offset(x * 4 + 3, 60))
Set TeamsFourthRow = Range(CrosstableCorner.Offset(x * 4 + 4, 1), CrosstableCorner.Offset(x * 4 + 4, 60))
Set BothTeams = Range(CrosstableCorner.Offset(x * 4 + 3, 1), CrosstableCorner.Offset(x * 4 + 4, 60))
Dim y As Integer
y = 1

If Application.CountA(TeamsFourthRow) = 0 Then
TeamsThirdRow.EntireRow.Hidden = Not TeamsThirdRow.EntireRow.Hidden
Else
TeamsThirdRow.EntireRow.Hidden = Not TeamsThirdRow.EntireRow.Hidden
TeamsFourthRow.EntireRow.Hidden = Not TeamsFourthRow.EntireRow.Hidden
End If
End Sub
*******************************************************

If Len(TeamsFourthRow.value) = 0 "" Then
Syntax error doesnt recognize 'Then'

If TeamsFourthRow.Value = "" Then
mismatched type

If IsEmpty(TeamsFourthRow.Value) Then
simply ingores the if statement, executes the else
in either case, I placed values in the range to test


THIS DOES WORK !!!
if application.counta(TeamsFourthRow.Value) = 0



NOW
I am trying to put a statement in that will prevent any unhidding at all
if both ranges are empty

[SuitedAces]

The y is a leftover that should have been omitted

Dim y As Integer
y = 1