Efficient way of removing lines?

daveisack

New Member
Joined
Jul 31, 2002
Messages
3
Hi,

I need to remove invalid lines from a block of data that has +- 35000 lines.
The data covers columns A through I.
The criteria for removing these invalid lines is if the value in column I=40B.
The valid lines have no fixed value in column I and thus i cannot use "advanced filter copy"
How can i loop through this range of data and remove these lines?

Thanks,

David
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
On 2002-09-01 15:16, daveisack wrote:
Hi,

I need to remove invalid lines from a block of data that has +- 35000 lines.
The data covers columns A through I.
The criteria for removing these invalid lines is if the value in column I=40B.
The valid lines have no fixed value in column I and thus i cannot use "advanced filter copy"
How can i loop through this range of data and remove these lines?

If you've thought of the advacned filter route, why not filter for not equal to 40B?

Alternatively, the asap addin has a conditional delete tool (under the Columns / Rows menu item). You can download the addin for free from:

http://www.asap-utilities.com

as does j-walk:

http://www.j-walk.com

last option - search here for some code using keywords 'conditional row delete'

If, for example, your values are text, not formula generated, something like this:

<pre>

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo fin
If Target.Column = 9 And Target.Value = "40B" Then
Rows(Target.Row).Delete
End If
fin:
End Sub


</pre>

(above lifted from:

http://mrexcel.com/board/viewtopic.php?topic=13522&forum=2 )


Paddy
 

daveisack

New Member
Joined
Jul 31, 2002
Messages
3
Hi,

Thanks for that.Ho do i use the advanced filter copy to have criteria not equal to?

Regards,

David
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
Thanks for that.Ho do i use the advanced filter copy to have criteria not equal to?

One way:

1) apply a normal filter - data | filter auto filter

2) select the filter in the column of interest (J in this case), go to custom

3) change the 'show where rows' to 'does not equal'

4) in the next drop down, select the value of interest (40B in this case), press OK

5) Copy data...


Paddy
 

Forum statistics

Threads
1,144,330
Messages
5,723,749
Members
422,513
Latest member
Meathead2022

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