Deleting rows based on number of characters
 

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

Thanks much,
Mike Catalfamo


--
catalfamo1220
------------------------------------------------------------------------
catalfamo1220's Profile: http://www.excelforum.com/member.php...o&userid=29458
View this thread: http://www.excelforum.com/showthread...hreadid=563373

Deleting rows based on number of characters
 

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

Thanks much,
Mike Catalfamo

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

and hit CTRL-SHIFT-ENTER.

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

Scott


--
Maistrye
------------------------------------------------------------------------
Maistrye's Profile: http://www.excelforum.com/member.php...o&userid=36078
View this thread: http://www.excelforum.com/showthread...hreadid=563373

Deleting rows based on number of characters
 

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

and hit CTRL-SHIFT-ENTER.

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

Scott

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

Mike


--
catalfamo1220
------------------------------------------------------------------------
catalfamo1220's Profile: http://www.excelforum.com/member.php...o&userid=29458
View this thread: http://www.excelforum.com/showthread...hreadid=563373

Deleting rows based on number of characters
 

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

Mike

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

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

After either of these two, just delete the first column and you should
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 do
it for you.

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

Scott


--
Maistrye
------------------------------------------------------------------------
Maistrye's Profile: http://www.excelforum.com/member.php...o&userid=36078
View this thread: http://www.excelforum.com/showthread...hreadid=563373