Deleting rows based on values of cells

[jqzhang]

I tried to delete some rows based on the values of cells but literally I don't know why it's not working. When I ran the Sub, all the rows were gone. That was not what I want.
Please take a look. (it works when the criteria are like numbers, not characters)
Thanks in advance.
jqzhang

Sub MuchBetterDelRows_S_Col()
On Error Resume Next
Range("A1").AutoFilter Field:=10, Criteria1:="<>""MA"""
If Err = 0 Then _
Range("A2", Range("A65536").End(xlUp)).SpecialCells(xlCellTypeVisible).EntireRow.Delete
On Error GoTo 0
ActiveSheet.AutoFilterMode = False
End Sub

 

[PHV]

And what about this ?
Range("A1").AutoFilter Field:=10, Criteria1:="<>MA"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[SkipVought]

Hi,

Works for me as
[tt]
Criteria1:="<>MA"
[/tt]
otherwise it is lloing for everything that is not equal to "MA"


Skip,
[sub]
[red]Be Advised![/red]
A wee deranged psychic may be runnin' around out there!
SMALL MEDUIM @ LARGE[/sub]

 

[AnotherHiggins]

Try this:

Sub Macro()
Range("A1").AutoFilter Field:=1, Criteria1:="<>ma"
Range("A2", Range("A65536").End(xlUp)).SpecialCells(xlCellTypeVisible).EntireRow.Delete
ActiveSheet.AutoFilterMode = False
End Sub

If that doesn't do what you want, can you describe what it is you expect the code to do vs. what it is actually doing?

[tt]_____
[blue]-John[/blue][/tt]

Help us help you. Please read FAQ181-2886 before posting.

 

[SkipVought]

John,

Beat you by a whisker on a Firday afternoon! ;-)


Skip,
[sub]
[red]Be Advised![/red]
A wee deranged psychic may be runnin' around out there!
SMALL MEDUIM @ LARGE[/sub]

 

[AnotherHiggins]

'Been a long week.

[tt]_____
[blue]-John[/blue][/tt]

Help us help you. Please read FAQ181-2886 before posting.

 

[jqzhang]

Hi, guys,
It's good and bad. Good thing is both ways are working, lower case or upper. Bad thing is I will have '1004' error. Another thing that I don't get. If no '1004', none of the code will be working; '1004' gives me the right output. However, if I change the criteria from '<>MA' to '<>M2' (another value), then M2 rows will be gone and MA rows come back. The other way around if I delete MA rows. I never experienced this kind of thing before. Here is part of the data:
col. H col. J
...
...
3239.16 79 M2
3239.16 79 MA
2765.34 25 M2
2765.34 25 MA
2676.91 52 M2
2676.91 52 MA
2616.89 90 M2
2616.89 90 MA
2531.43 77 M2
2531.43 77 MA
2433 48 M2
2433 48 MA
...
...

Thank you all, appreciate all your help.
jqzhang

 

[PHV]

What about this ?
Sub MuchBetterDelRows_S_Col()
Dim LastRow As Long
On Error Resume Next
LastRow = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
With Columns("J:J")
.AutoFilter Field:=1, Criteria1:="<>MA"
Range("J2:J" & LastRow).SpecialCells(xlCellTypeVisible).EntireRow.Delete
.AutoFilter
End With
End Sub

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886