Efficient way of removing lines?

[daveisack]

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

 

[PaddyD]

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]

Hi,

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

Regards,

David

 

[PaddyD]

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