Deleting rows based on number of characters

C

catalfamo1220

Is there a method that can be used to erase a row based on the number o
characters in it (all cells of that row)? In my specific situation, al
rows with less than 58 characters in it should be deleted.

Thanks much,
Mike Catalfam
 
M

Maistrye

catalfamo1220 said:
Is there a method that can be used to erase a row based on the number o
characters in it (all cells of that row)? In my specific situation, al
rows with less than 58 characters in it should be deleted.

Thanks much,
Mike Catalfamo
Click to expand...

If you have the room, add an extra column and put in the followin
formula. You can put it elsewhere, but for this example i'm assumin
column A:
=SUMPRODUCT(LEN(B2:IV2))

and hit CTRL-SHIFT-ENTER.

Then fill down, sort by column A, and delete the rows with fewe
characters than you want.

Scot
 
C

catalfamo1220

Maistrye said:
If you have the room, add an extra column and put in the followin
formula. You can put it elsewhere, but for this example i'm assumin
column A:
=SUMPRODUCT(LEN(B2:IV2))

and hit CTRL-SHIFT-ENTER.

Then fill down, sort by column A, and delete the rows with fewe
characters than you want.

Scott
Click to expand...

Thanks Scott, but I need something that will delete the row
automatically as I have +9000 rows of data. I might be misinterpretin
but it seems this only displays the number of characters.

Mik
 
M

Maistrye

catalfamo1220 said:
Thanks Scott, but I need something that will delete the row
automatically as I have +9000 rows of data. I might be misinterpretin
but it seems this only displays the number of characters.

Mike
Click to expand...

What I said before should display the number of characters on eac
line. You just sort by that, and then you do one delete to get rid o
everything you don't want (since they will all be contiguous rows).

Alternatively, instead of sorting, you can do an AutoFilter on tha
column (the one with the lengths) with custom criteria to show all row
that have total length less than your threshold, and delete those. Thi
method will preserve the order.

After either of these two, just delete the first column and you shoul
be back to normal without the rows you don't want.

And if you want it really automatic, you'll have to write a macro to d
it for you.

For the macro, you'd be best to loop through all the rows from last t
first and delete the lines one by one. (First to last introduces extr
complications)

Scot
 
Top