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
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:Originally Posted by catalfamo1220
=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.Originally Posted by Maistrye
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).Originally Posted by catalfamo1220
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
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks