| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Confirm Empty Cells in a Range
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("C1:D3,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("C1:D3,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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Confirm Empty Cells in a Range
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("C1:D3,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("C1:D3,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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Confirm Empty Cells in a Range
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("C1:D3,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("C1:D3,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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Confirm Empty Cells in a Range
How about a loop??:
Sub ryan() AreTheyAllEmpty = True Set r = Range("C1:D3,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("C1:D3,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("C1:D3,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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Confirm Empty Cells in a Range
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("C1:D3,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("C1:D3,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("C1:D3,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("C1:D3,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("C1:D3,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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Confirm Empty Cells in a Range
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("C1:D3,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("C1:D3,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("C1:D3,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("C1:D3,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("C1:D3,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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Confirm Data from Other Cells | Excel Programming | |||
| Empty range of cells | Excel Programming | |||
| Count Empty Cells in Range After Cells with Data | Excel Programming | |||
| Counting empty cells within a range of cells | New Users to Excel | |||
| sum next two non-empty cells in a range | Excel Worksheet Functions | |||
Linear Mode
