Deleteng Lines

[Eddie Wildon]

Good morning everyone

I have a toughie that, after about 2 hours, I cannot seem to solve.

I have a worksheet spanning 54000 lines and need to weed out most of it to get at what I really want.

Several of the lines contain identical data but I cannot simply sort it because I need to maintain names above data.

Is there a script that I can use to look at each line, and if it contains certain text to delete the whole line, and then move on to the next line, all that way to the end of the report?

This will maintain the order that the data is in, while removeing unwanted lines. Any help on this would be appreciated.

JCA

 

[SteveO59L]

Is the data you want to delete, held in one cell, or many cells ?

 

[Eddie Wildon]

The entire line where specific text is found. If I have the option to change what the script is looking for when it's checking each line, I can repeat until all the bad lines are gone.

 

[Peter_SSs]

Eddie Wildon

You may be able to adapt this solution from another current thread. Otherwise, spell out in more detail what needs to be found (and where) to delete the rows.
http://www.mrexcel.com/board2/viewtopic.php?p=1119987#1119987

 

[Eddie Wildon]

Ok, using the script found on that thread, my working range is a6:a57599

if text 'Do the hokey pokey' is found in any cell, delete the entire row that it's found on.

Hope that makes alittle more sense.

 

[Peter_SSs]

So, something like this should do it then:

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> DeleteRows()
    <SPAN style="color:#00007F">Dim</SPAN> LastRow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> Row <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN>
    Application.EnableEvents = <SPAN style="color:#00007F">False</SPAN>
    LastRow = Range("A65536").End(xlUp).Row
    <SPAN style="color:#00007F">For</SPAN> Row = LastRow <SPAN style="color:#00007F">To</SPAN> 6 <SPAN style="color:#00007F">Step</SPAN> -1
        <SPAN style="color:#00007F">If</SPAN> Cells(Row, 1).Value = "Do the hokey pokey" <SPAN style="color:#00007F">Then</SPAN>
            Cells(Row, 1).EntireRow.Delete
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
    <SPAN style="color:#00007F">Next</SPAN> Row
    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN>
    Application.EnableEvents = <SPAN style="color:#00007F">True</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[Eddie Wildon]

not only did it work beautifully, you answered my next and last q as to why it wouldn't work for me. (my range was wrong)

Kudos to you and my thanks! (58000 lines is a LOT of manual labor)

Pity there isn't a rating system on this board...

 

[calgaryguy]

I don't want to intrude on this posting but I think I also have a similar problem. Instead of deleting a whole row, I would only want to delete 3 cells. There may be more info on the left or the right that I would like to keep. If the cell in the middle equals zero, I would want the 3 cells deleted. Thanks if anyone can help me with this.

Ken

 

[Peter_SSs]

I don't want to intrude on this posting but I think I also have a similar problem. Instead of deleting a whole row, I would only want to delete 3 cells. There may be more info on the left or the right that I would like to keep. If the cell in the middle equals zero, I would want the 3 cells deleted. Thanks if anyone can help me with this.

Ken

Ken

1. Which column do you want to check for 0 in?
2. Do you just want to delete the values from the 3 cells? .. or actually delete the cells and move other cells up/left etc?

 

[calgaryguy]

Well, first off, I want to actually delete all 3 cells and move them up. I want to do this for 7 sets of 3 across. So I guess I have 21 columns. The columns that could have zero would be in B, E, H, K, N, Q, T. If the value is 0 in B7, I would want to delete A7, B7 & C7 and move everything up one row. I do not want to delete any other cells. Similarly, if Q7 is 0, I would only want to delete P7, Q7 & R7.

Thanks for your help.

Ken