Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
DELETE ALL EXCEPT LETTER OF A WORD | Excel Discussion (Misc queries) | |||
delete series of letter in spreadsheet | Excel Worksheet Functions | |||
Delete row based on letter | Excel Programming | |||
how to delete the 1st letter form a set of rows of info. | Excel Worksheet Functions | |||
Delete Rows with letter #VALUE in cell J | Excel Programming |