Macro if greater than letter then delete
 

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
--
Mike B

Macro if greater than letter then delete
 

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

--
Regards,
Tom Ogilvy





"mike b" wrote in message
...
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
--
Mike B

Macro if greater than letter then 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

"Tom Ogilvy" wrote:

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

--
Regards,
Tom Ogilvy





"mike b" wrote in message
...
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
--
Mike B

Macro if greater than letter then delete
 

try this

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

--
Don Guillett
SalesAid Software

"mike b" wrote in message
...
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
--
Mike B

Macro if greater than letter then delete
 

what does the 67 do for me in the code?

"Don Guillett" wrote:

try this

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

--
Don Guillett
SalesAid Software

"mike b" wrote in message
...
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
--
Mike B

Macro if greater than letter then delete
 

change to c.ENTIRErow.delete

--
Don Guillett
SalesAid Software

"Don Guillett" wrote in message
...
try this

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

--
Don Guillett
SalesAid Software

"mike b" wrote in message
...
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
--
Mike B

Macro if greater than letter then delete
 

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

--
Don Guillett
SalesAid Software

"mike b" wrote in message
...
what does the 67 do for me in the code?

"Don Guillett" wrote:

try this

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

--
Don Guillett
SalesAid Software

"mike b" wrote in message
...
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
--
Mike B

Macro if greater than letter then delete
 

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" wrote:

change to c.ENTIRErow.delete

--
Don Guillett
SalesAid Software

"Don Guillett" wrote in message
...
try this

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

--
Don Guillett
SalesAid Software

"mike b" wrote in message
...
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
--
Mike B

Macro if greater than letter then delete
 

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

"mike b" wrote in message
...
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" wrote:

change to c.ENTIRErow.delete

--
Don Guillett
SalesAid Software

"Don Guillett" wrote in message
...
try this

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

--
Don Guillett
SalesAid Software

"mike b" wrote in message
...
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
--
Mike B

Macro if greater than letter then delete
 

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

--
Regards,
Tom Ogilvy

"Don Guillett" wrote in message
...
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

"mike b" wrote in message
...
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" wrote:

change to c.ENTIRErow.delete

--
Don Guillett
SalesAid Software

"Don Guillett" wrote in message
...
try this

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

--
Don Guillett
SalesAid Software

"mike b" wrote in message
...
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
--
Mike B

Macro if greater than letter then delete
 

I told you that already.

--
Regards,
Tom Ogilvy

"mike b" wrote in message
...
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

"Tom Ogilvy" wrote:

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

--
Regards,
Tom Ogilvy





"mike b" wrote in message
...
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
--
Mike B

Macro if greater than letter then delete
 

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

--
Don Guillett
SalesAid Software

"Tom Ogilvy" wrote in message
...
This misses rows for me. Also errors on empty cells. (xl97 for that)

--
Regards,
Tom Ogilvy

"Don Guillett" wrote in message
...
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

"mike b" wrote in message
...
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" wrote:

change to c.ENTIRErow.delete

--
Don Guillett
SalesAid Software

"Don Guillett" wrote in message
...
try this

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

--
Don Guillett
SalesAid Software

"mike b" wrote in message
...
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
--
Mike B