Simple delete rows based on a cells value....

[GS7CLW]

Happy hump day!

I am simply trying to delete all rows that have a value of less than 16 in the cell value's first 2 characters (column G).
start with:
18N
18K
16K
16K
16K
14L
14L
13L
12L
end with:
18N
18K
16K
16K
16K
the code I am using:

Sub DeleteUnneededRows()
Dim c
For Each c In Worksheets("Sheet1").Range("G1:G500").Cells
        If Left(c.Value, 2) < "16" Then
        c.EntireRow.Delete
        End If
    Next
End Sub

but it is not working.

ANY & ALL help appreciated.

 

[VoG]

Try

Sub DeleteUnneededRows()
Dim c As Range
With Worksheets("Sheet1")
    For Each c In .Range("G1:G500")
        If Val(Left(c.Value, 2)) < 16 Then
            c.EntireRow.Delete
        End If
        Next
End With
End Sub

 

[Jonmo1]

There's 2 problems.

1. The Left function returns a TEXT string, and doing a < comparison on a text string just makes no sense.
2. You have to go backwards, from the bottom to the top. Otherwise rows get skipped.

Try

Sub test()
Dim i As Long
For i = 500 To 1 Step -1
    If CLng(Left(Cells(i, "G"), 2)) < 16 Then
        Rows(i).EntireRow.Delete
    End If
Next i
End Sub

 

[GS7CLW]

Vog

yours appears to be going thru the code correctly but nothing actually happens.

Jonmo

I get a type mismatch err msg with yours.

 

[Jonmo1]

Jonmo

I get a type mismatch err msg with yours.

It works for me with the sample data you posted.
In your Actual data, do ALL the values begin with 2 numbers?
Are there any formula error values in the data, like #N/A #VALUE! #DIV/0! ?

 

[Jonmo1]

I see the problem..

Are there any Blanks in your range G1:G500 ?
Is the 500 just a made up number that is beyond the end of your data?

Try this way to explicitly determine where the data ends in column G

Sub test()
Dim i As Long, lr As Long
lr = Cells(Rows.Count, "G").End(xlUp).Row
For i = lr To 1 Step -1
    If CLng(Left(Cells(i, "G"), 2)) < 16 Then
        Rows(i).EntireRow.Delete
    End If
Next i
End Sub

 

[GS7CLW]

no blank rows and it works perfectly.

THANK YOU@!!!@

have a great day.

 

[Jonmo1]

Glad to help, thanks for the feedback.