Macro if greater than letter then delete

  • Thread starter Thread starter Guest
  • Start date Start date
G

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

Sub deleteifbadgrade()
For Each c In Selection
If Asc(UCase(c)) > 67 Then c.row.delete
Next
End Sub
 
what does the 67 do for me in the code?

Don Guillett said:
try this

Sub deleteifbadgrade()
For Each c In Selection
If Asc(UCase(c)) > 67 Then c.row.delete
Next
End Sub
Click to expand...
 
look in vba HELP index for ASC. Try it > you will like it with the change to
entirerow
 
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
 
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)
mike b said:
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
Click to expand...
 
I told you that already.

--
Regards,
Tom Ogilvy

mike b said:
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
Click to expand...
 
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
 
Back
Top