Delete Row if Cell Contains More Than 10 Characters

[Ike]

Hello All,

I've been searching for a code that will simply do the following.

I have a spreadsheet that has both shipment tracking numbers and serial numbers in the same column.

If in column "N" a cell is greater than 10 characters then delete the entire row. I want to keep all serial numbers that are 10 characters long without spaces or special characters.

The cells in the column will contain both alpha and numeric characters.

This code will be inserted in an existing VBA macro.

Thank you very much.

Ike

[Bob Phillips]

With Activesheet

    lastrow = .Cells(.Rows.Count, "N").End(xlUp).Row
    For i = lastrow to 2 Step -1

        If Len(.Cells(i, "N").Value) > 10 Then

            .Rows(i).Delete
       End If
    Next i

[mancubus]

hey ike. you mean spaces and special characters will not be taken into account when calculating the lengt of a string? (and what special characters?)

[snb]

Sub M_snb()
    [N1:N100] = [if(N1:N100="","",if(len(N1:N100)>10,"",N1:N100))]
    Columns(14).SpecialCells(4).EntireRow.Delete
End Sub

[Ike]

Hi Mancubus.

Sometimes when data is brought over from a website a cell can expand and appear to make a row height twice the size (from 12.75 to 25.5) because for instance...

If you type a letter, let's say "A" and then press ALT/Enter there is now an invisible special character that takes up space and when a vlookup from another source is executed on that cell the result will be #N/A. The same result occurs when there is a space either in front of the string of 10 characters or at the end.

Thank you for asking

[mancubus]

you are welcome. so you need to "clean" the strings first. after that you can use either procedure posted by xld and snb. ----- googling "excel vba to remove special characters from string" gives many examples.

[Ike]

Thanks again to everyone. This is solved.