PDA

View Full Version : [SOLVED] Delete Row if Cell Contains More Than 10 Characters



Ike
02-10-2014, 06:48 AM
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
02-10-2014, 07:40 AM
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
02-10-2014, 07:46 AM
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
02-10-2014, 08:05 AM
Sub M_snb()
[N1:N100] = [if(N1:N100="","",if(len(N1:N100)>10,"",N1:N100))]
Columns(14).SpecialCells(4).EntireRow.Delete
End Sub

Ike
02-10-2014, 08:25 AM
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
02-10-2014, 08:38 AM
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
02-10-2014, 08:41 AM
Thanks again to everyone. This is solved.