Delete if blank...
 

Hi all,

I am trying to delete an entire row if a cell in col F is blank and i
the corresponding cell in col E does NOT equal a certain list of names
Here are two different pieces of code that i have been trying to use bu
they don't seem to work. The second piece of code worked in anothe
worksheet using different cols.

If col F has a blank cell, this piece of code deletes the entire row n
matter what name is in col E:

Dim i, j As Long
j = Range("E65536").End(xlUp).Row
For i = j To 1 Step -1
If Range("E" & i).Value < "Bertrandk" _
Or Range("E" & i).Value < "christianp" _
Or Range("E" & i).Value < "dennisk" _
Or Range("E" & i).Value < "josephl" _
Or Range("E" & i).Value < "mariaf" _
Or Range("E" & i).Value < "vincentp" Then
If Range("F" & i).Value = "" Then
Rows(i).EntireRow.Delete
End If
End If
Next i



This piece of code does not delete or keep any row if it has a blan
cell in col F:

Dim c As Range

For Each c In Range("F:F").SpecialCells(xlCellTypeBlanks)
If c.Offset(0, -1) = "Bertrandk" _
Or c.Offset(0, -1) = "christianp" _
Or c.Offset(0, -1) = "dennisk" _
Or c.Offset(0, -1) = "josephl" _
Or c.Offset(0, -1) = "mariaf" _
Or c.Offset(0, -1) = "vincentp" Then
'do nothing
Else
c.Offset(0, 45).Value = 1
End If
Next c

On Error Resume Next 'ignore error
Selection.Offset(0
45).SpecialCells(xlCellTypeConstants).EntireRow.De lete


Could someone please enlighten me as to what i am doin wrong in eithe
of these pieces of code??

Thanks,
Cathal

--
Message posted from http://www.ExcelForum.com

Delete if blank...
 

Try changing your "Or"s to "And"s because if it is equal to any one of the
names, then it is not equal to all the rest and therefore the statement is
true.
Also you can add the blank test as an "And" to the list of names.

If Range("E" & i).Value < "Bertrandk" _
And Range("E" & i).Value < "christianp" _
And Range("E" & i).Value < "dennisk" _
And Range("E" & i).Value < "josephl" _
And Range("E" & i).Value < "mariaf" _
And Range("E" & i).Value < "vincentp" Then
And Range("F" & i).Value = "" Then
Rows(i).EntireRow.Delete
End If
Next i



"CPower " wrote in message
...
Hi all,

I am trying to delete an entire row if a cell in col F is blank and if
the corresponding cell in col E does NOT equal a certain list of names.
Here are two different pieces of code that i have been trying to use but
they don't seem to work. The second piece of code worked in another
worksheet using different cols.

If col F has a blank cell, this piece of code deletes the entire row no
matter what name is in col E:

Dim i, j As Long
j = Range("E65536").End(xlUp).Row
For i = j To 1 Step -1
If Range("E" & i).Value < "Bertrandk" _
Or Range("E" & i).Value < "christianp" _
Or Range("E" & i).Value < "dennisk" _
Or Range("E" & i).Value < "josephl" _
Or Range("E" & i).Value < "mariaf" _
Or Range("E" & i).Value < "vincentp" Then
If Range("F" & i).Value = "" Then
Rows(i).EntireRow.Delete
End If
End If
Next i



This piece of code does not delete or keep any row if it has a blank
cell in col F:

Dim c As Range

For Each c In Range("F:F").SpecialCells(xlCellTypeBlanks)
If c.Offset(0, -1) = "Bertrandk" _
Or c.Offset(0, -1) = "christianp" _
Or c.Offset(0, -1) = "dennisk" _
Or c.Offset(0, -1) = "josephl" _
Or c.Offset(0, -1) = "mariaf" _
Or c.Offset(0, -1) = "vincentp" Then
'do nothing
Else
c.Offset(0, 45).Value = 1
End If
Next c

On Error Resume Next 'ignore error
Selection.Offset(0,
45).SpecialCells(xlCellTypeConstants).EntireRow.De lete


Could someone please enlighten me as to what i am doin wrong in either
of these pieces of code??

Thanks,
Cathal.


---
Message posted from http://www.ExcelForum.com/

Delete if blank...
 

Brillant thanks a mill, worked perfectly

--
Message posted from http://www.ExcelForum.com

Delete if blank...
 

As Paul pointed out, your problem with the first routine is the use of OR when
it should be AND.

You might want to try a Select Case block here. It should also be faster to
execute, since you retrieve the cell value only once instead of once for each
name to be excluded (6 times in all).

Option Compare Text
Dim i As Long, j As Long
j = Range("E65536").End(xlUp).Row
For i = j To 1 Step -1
Select Case Cells(i, 5).Value
Case "Bertrandk", "christianp", "dennisk", "josephl", "mariaf", "vincentp"
'don't do anything
Case Else
If Cells(i, 6).Value = "" Then Rows(i).EntireRow.Delete
End Select
Next i

Accessing the worksheet creates a bottleneck in code, so even it you don't use
Select Case, it would be a good idea to retrieve the value from the cell, put
it in a variable, and compare *the variable* with your list. i.e.

v = Cells(i, 5).Value
If v < "Bertrandk" And v < "christianp" And .....

Yet another approach is to put the names into an array, and use MATCH to check
the name, i.e.

Dim i As Long, j As Long
Dim NameList as Variant

NameList = Array("Bertrandk", "christianp", "dennisk", _
"josephl", "mariaf", "vincentp")
j = Range("E65536").End(xlUp).Row
For i = j To 1 Step -1
If IsError(Application.Match(Cells(i, 5).Value, NameList, 0)) Then
If Cells(i, 6).Value = "" Then Rows(i).EntireRow.Delete
End If
Next i

As for your second version not working, try it as

Columns("F").Offset(0, 45) _
.SpecialCells(xlCellTypeConstants).EntireRow.Delet e

On Tue, 10 Aug 2004 03:22:02 -0500, CPower
wrote:

Hi all,

I am trying to delete an entire row if a cell in col F is blank and if
the corresponding cell in col E does NOT equal a certain list of names.
Here are two different pieces of code that i have been trying to use but
they don't seem to work. The second piece of code worked in another
worksheet using different cols.

If col F has a blank cell, this piece of code deletes the entire row no
matter what name is in col E:

Dim i, j As Long
j = Range("E65536").End(xlUp).Row
For i = j To 1 Step -1
If Range("E" & i).Value < "Bertrandk" _
Or Range("E" & i).Value < "christianp" _
Or Range("E" & i).Value < "dennisk" _
Or Range("E" & i).Value < "josephl" _
Or Range("E" & i).Value < "mariaf" _
Or Range("E" & i).Value < "vincentp" Then
If Range("F" & i).Value = "" Then
Rows(i).EntireRow.Delete
End If
End If
Next i



This piece of code does not delete or keep any row if it has a blank
cell in col F:

Dim c As Range

For Each c In Range("F:F").SpecialCells(xlCellTypeBlanks)
If c.Offset(0, -1) = "Bertrandk" _
Or c.Offset(0, -1) = "christianp" _
Or c.Offset(0, -1) = "dennisk" _
Or c.Offset(0, -1) = "josephl" _
Or c.Offset(0, -1) = "mariaf" _
Or c.Offset(0, -1) = "vincentp" Then
'do nothing
Else
c.Offset(0, 45).Value = 1
End If
Next c

On Error Resume Next 'ignore error
Selection.Offset(0,
45).SpecialCells(xlCellTypeConstants).EntireRow.D elete


Could someone please enlighten me as to what i am doin wrong in either
of these pieces of code??

Thanks,
Cathal.


---
Message posted from http://www.ExcelForum.com/