Macro if greater than letter then delete

[Guest]

Hello,

I need a macro that can look at columns F, G and H, if any of those columns
have a letters D-, D, D+ or E then delete the row.

Thanks for your help

 

[Tom Ogilvy]

Sub EFGH()
Dim rngC As Range, rngF As Range
Dim rng As Range, rng1 As Range
Dim rw As Range
On Error Resume Next
Set rngC = Range("F:H").SpecialCells(xlConstants)
Set rngF = Range("F:H").SpecialCells(xlFormulas)
If rngC Is Nothing And rngF Is Nothing Then Exit Sub
If Not rngC Is Nothing And Not rngF Is Nothing Then
Set rng = Intersect(rngC, rngF)
ElseIf rngF Is Nothing Then
Set rng = rngC
Else
Set rng = rngF
End If
On Error GoTo 0
Set rng = Intersect(rng.EntireRow, Range("F:H"))
For Each rw In rng.Rows
If Application.CountIf(rw, "D*") > 0 Or Application.CountIf(rw, "E*") > 0
Then
If rng1 Is Nothing Then
Set rng1 = rw
Else
Set rng1 = Union(rng1, rw)
End If
End If
Next
If Not rng1 Is Nothing Then
rng1.EntireRow.Select
End If
End Sub

If that identifies the rows you want to delete, then change
rng1.EntireRow.Select to rng1.EntireRow.Delete

 

[Guest]

If the code is left as is will it delete the row when it finds one of the
letters to delete? If not what line of the code do I need to change?

Thanks
Mike

 

[Don Guillett]

try this

Sub deleteifbadgrade()
For Each c In Selection
If Asc(UCase(c)) > 67 Then c.row.delete
Next
End Sub

 

[Guest]

what does the 67 do for me in the code?

try this

Sub deleteifbadgrade()
For Each c In Selection
If Asc(UCase(c)) > 67 Then c.row.delete
Next
End Sub

 

[Don Guillett]

change to c.ENTIRErow.delete

 

[Don Guillett]

look in vba HELP index for ASC. Try it > you will like it with the change to
entirerow

 

[Guest]

Don, sorry I'm getting confused. I am alos trying to learn how to program as
well.

What would be the macro code If I were to only look at column F for a letter
D or E and then delete the whole entire row?

Thanks

 

[Don Guillett]

try this for f,g & h or chg to f for only f

Sub deleteifbadgrade()
For Each c In range("f2:H" & cells(rows.count,"f").end(xlup).row)
If Asc(UCase(c)) > 67 Then c.entirerow.delete
Next
End Sub


--
Don Guillett
SalesAid Software
(e-mail address removed)

Don, sorry I'm getting confused. I am alos trying to learn how to program as
well.

What would be the macro code If I were to only look at column F for a letter
D or E and then delete the whole entire row?

Thanks

 

[Tom Ogilvy]

This misses rows for me. Also errors on empty cells. (xl97 for that)

 

[Tom Ogilvy]

I told you that already.

--
Regards,
Tom Ogilvy

If the code is left as is will it delete the row when it finds one of the
letters to delete? If not what line of the code do I need to change?

Thanks
Mike

 

[Don Guillett]

Tom,

I tested with xl2002 with no blanks but try this, from the bottom up, for
col F

Sub deleteifasccode1()
For i = Cells(Rows.Count, "f").End(xlUp).Row To 1 Step -1
On Error Resume Next
If Asc(UCase(Cells(i, "f"))) > 67 Then Rows(i).Delete
Next i
End Sub