Deleteng Lines

Eddie Wildon

Board Regular
Joined
Jul 29, 2005
Messages
83
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
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

Eddie Wildon

Board Regular
Joined
Jul 29, 2005
Messages
83
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.
 

Eddie Wildon

Board Regular
Joined
Jul 29, 2005
Messages
83

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,431
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Jul 29, 2005
Messages
83

ADVERTISEMENT

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

Board Regular
Joined
Jul 10, 2006
Messages
116
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,431
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Jul 10, 2006
Messages
116
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
 

Forum statistics

Threads
1,136,431
Messages
5,675,806
Members
419,586
Latest member
RoteichA

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top