I have confirmed that Range("B10") Interior Color Index is = 50 and the Range
I am including in the If...Then Statement is empty, but when I run this
portion of code the MsgBox does not appear, it executes the Else portion of
the If...Then, Why? Any suggestions would be greatly appreciated.

'removes all jobs on quote sheet
If Range("B10").Interior.ColorIndex = 50 And
IsEmpty(Range("C13,C5:G8,G1:H3")) = True Then

MsgBox "You don't have anything to delete on your quote sheet.",
vbCritical
Exit Sub

Else

'clears contents of customer information
Range("C13,C5:G8,G1:H3").ClearContents

'clears all quotes
For n = 10 To 200
If Cells(n, 2).Interior.ColorIndex = 50 Then
LastRow = Cells(n, 2).Row - 1
Exit For
End If
Next n

FirstRow = Range("B10").Row
Rows(FirstRow & ":" & LastRow).Delete Shift:=xlUp

End If

Thanks in Advance,
Ryan

Isempty() is usually used on variants. Something like:

Range("A1").value

So your code is actually working - but its not checking the values in the
cells, its checking if the Range is empty.

In any case you should decide if you want the True path to be taken if ANY
cell in the range is empty or if ALL the cells in the range are empty.
--
Gary''s Student - gsnu200784


"RyanH" wrote:

> I have confirmed that Range("B10") Interior Color Index is = 50 and the Range
> I am including in the If...Then Statement is empty, but when I run this
> portion of code the MsgBox does not appear, it executes the Else portion of
> the If...Then, Why? Any suggestions would be greatly appreciated.
>
> 'removes all jobs on quote sheet
> If Range("B10").Interior.ColorIndex = 50 And
> IsEmpty(Range("C13,C5:G8,G1:H3")) = True Then
>
> MsgBox "You don't have anything to delete on your quote sheet.",
> vbCritical
> Exit Sub
>
> Else
>
> 'clears contents of customer information
> Range("C13,C5:G8,G1:H3").ClearContents
>
> 'clears all quotes
> For n = 10 To 200
> If Cells(n, 2).Interior.ColorIndex = 50 Then
> LastRow = Cells(n, 2).Row - 1
> Exit For
> End If
> Next n
>
> FirstRow = Range("B10").Row
> Rows(FirstRow & ":" & LastRow).Delete Shift:=xlUp
>
> End If
>
> Thanks in Advance,
> Ryan

I want it to check if all the cells are empty. Is that possible with what i
have or do I try to go about it another way?

Thanks
Ryan

"Gary''s Student" wrote:

> Isempty() is usually used on variants. Something like:
>
> Range("A1").value
>
> So your code is actually working - but its not checking the values in the
> cells, its checking if the Range is empty.
>
> In any case you should decide if you want the True path to be taken if ANY
> cell in the range is empty or if ALL the cells in the range are empty.
> --
> Gary''s Student - gsnu200784
>
>
> "RyanH" wrote:
>
> > I have confirmed that Range("B10") Interior Color Index is = 50 and the Range
> > I am including in the If...Then Statement is empty, but when I run this
> > portion of code the MsgBox does not appear, it executes the Else portion of
> > the If...Then, Why? Any suggestions would be greatly appreciated.
> >
> > 'removes all jobs on quote sheet
> > If Range("B10").Interior.ColorIndex = 50 And
> > IsEmpty(Range("C13,C5:G8,G1:H3")) = True Then
> >
> > MsgBox "You don't have anything to delete on your quote sheet.",
> > vbCritical
> > Exit Sub
> >
> > Else
> >
> > 'clears contents of customer information
> > Range("C13,C5:G8,G1:H3").ClearContents
> >
> > 'clears all quotes
> > For n = 10 To 200
> > If Cells(n, 2).Interior.ColorIndex = 50 Then
> > LastRow = Cells(n, 2).Row - 1
> > Exit For
> > End If
> > Next n
> >
> > FirstRow = Range("B10").Row
> > Rows(FirstRow & ":" & LastRow).Delete Shift:=xlUp
> >
> > End If
> >
> > Thanks in Advance,
> > Ryan

How about a loop??:

Sub ryan()
AreTheyAllEmpty = True
Set r = Range("C13,C5:G8,G1:H3")
For Each rr In r
If IsEmpty(rr.Value) Then
Else
AreTheyAllEmpty = False
End If
Next
End Sub

--
Gary''s Student - gsnu200784


"RyanH" wrote:

> I want it to check if all the cells are empty. Is that possible with what i
> have or do I try to go about it another way?
>
> Thanks
> Ryan
>
> "Gary''s Student" wrote:
>
> > Isempty() is usually used on variants. Something like:
> >
> > Range("A1").value
> >
> > So your code is actually working - but its not checking the values in the
> > cells, its checking if the Range is empty.
> >
> > In any case you should decide if you want the True path to be taken if ANY
> > cell in the range is empty or if ALL the cells in the range are empty.
> > --
> > Gary''s Student - gsnu200784
> >
> >
> > "RyanH" wrote:
> >
> > > I have confirmed that Range("B10") Interior Color Index is = 50 and the Range
> > > I am including in the If...Then Statement is empty, but when I run this
> > > portion of code the MsgBox does not appear, it executes the Else portion of
> > > the If...Then, Why? Any suggestions would be greatly appreciated.
> > >
> > > 'removes all jobs on quote sheet
> > > If Range("B10").Interior.ColorIndex = 50 And
> > > IsEmpty(Range("C13,C5:G8,G1:H3")) = True Then
> > >
> > > MsgBox "You don't have anything to delete on your quote sheet.",
> > > vbCritical
> > > Exit Sub
> > >
> > > Else
> > >
> > > 'clears contents of customer information
> > > Range("C13,C5:G8,G1:H3").ClearContents
> > >
> > > 'clears all quotes
> > > For n = 10 To 200
> > > If Cells(n, 2).Interior.ColorIndex = 50 Then
> > > LastRow = Cells(n, 2).Row - 1
> > > Exit For
> > > End If
> > > Next n
> > >
> > > FirstRow = Range("B10").Row
> > > Rows(FirstRow & ":" & LastRow).Delete Shift:=xlUp
> > >
> > > End If
> > >
> > > Thanks in Advance,
> > > Ryan

I agree....using COUNTA() is better
--
Gary''s Student - gsnu200784


"RyanH" wrote:

> Thanks for you help! I forgot about the CountA Function. I decided to use
> this.
>
> 'removes all jobs on quote sheet
> If Range("B10").Interior.ColorIndex = 50 And
> Application.CountA(Range("C13,C5:G8,G1:H3")) = 0 Then
>
> MsgBox "You don't have anything to delete on your quote sheet.",
> vbCritical
> Exit Sub
>
> Else
>
> 'clears contents of customer information
> Range("C13,C5:G8,G1:H3").ClearContents
>
> 'clears all quotes
> If Range("B10").Interior.ColorIndex <> 50 Then
> For n = 10 To 200
> If Cells(n, 2).Interior.ColorIndex = 50 Then
> LastRow = Cells(n, 2).Row - 1
> Exit For
> End If
> Next n
>
> FirstRow = Range("B10").Row
> Rows(FirstRow & ":" & LastRow).Delete Shift:=xlUp
> End If
> End If
>
> Thanks
> Ryan
>
>
> "Gary''s Student" wrote:
>
> > How about a loop??:
> >
> > Sub ryan()
> > AreTheyAllEmpty = True
> > Set r = Range("C13,C5:G8,G1:H3")
> > For Each rr In r
> > If IsEmpty(rr.Value) Then
> > Else
> > AreTheyAllEmpty = False
> > End If
> > Next
> > End Sub
> >
> > --
> > Gary''s Student - gsnu200784
> >
> >
> > "RyanH" wrote:
> >
> > > I want it to check if all the cells are empty. Is that possible with what i
> > > have or do I try to go about it another way?
> > >
> > > Thanks
> > > Ryan
> > >
> > > "Gary''s Student" wrote:
> > >
> > > > Isempty() is usually used on variants. Something like:
> > > >
> > > > Range("A1").value
> > > >
> > > > So your code is actually working - but its not checking the values in the
> > > > cells, its checking if the Range is empty.
> > > >
> > > > In any case you should decide if you want the True path to be taken if ANY
> > > > cell in the range is empty or if ALL the cells in the range are empty.
> > > > --
> > > > Gary''s Student - gsnu200784
> > > >
> > > >
> > > > "RyanH" wrote:
> > > >
> > > > > I have confirmed that Range("B10") Interior Color Index is = 50 and the Range
> > > > > I am including in the If...Then Statement is empty, but when I run this
> > > > > portion of code the MsgBox does not appear, it executes the Else portion of
> > > > > the If...Then, Why? Any suggestions would be greatly appreciated.
> > > > >
> > > > > 'removes all jobs on quote sheet
> > > > > If Range("B10").Interior.ColorIndex = 50 And
> > > > > IsEmpty(Range("C13,C5:G8,G1:H3")) = True Then
> > > > >
> > > > > MsgBox "You don't have anything to delete on your quote sheet.",
> > > > > vbCritical
> > > > > Exit Sub
> > > > >
> > > > > Else
> > > > >
> > > > > 'clears contents of customer information
> > > > > Range("C13,C5:G8,G1:H3").ClearContents
> > > > >
> > > > > 'clears all quotes
> > > > > For n = 10 To 200
> > > > > If Cells(n, 2).Interior.ColorIndex = 50 Then
> > > > > LastRow = Cells(n, 2).Row - 1
> > > > > Exit For
> > > > > End If
> > > > > Next n
> > > > >
> > > > > FirstRow = Range("B10").Row
> > > > > Rows(FirstRow & ":" & LastRow).Delete Shift:=xlUp
> > > > >
> > > > > End If
> > > > >
> > > > > Thanks in Advance,
> > > > > Ryan