VB, Need to Delete rows

Solved/Closed
weenie - Jul 22, 2011 at 08:54 PM
 weenie - Jul 25, 2011 at 11:56 PM
Hello,

I have data in Sheet2 up to 900,000 rows and to Column Q (the values column). I'm not sure how to write a VB that will look in column Q and if it does not match these values 1.0E+17, 1.0E+30, 1.5E+30 THEN delete rows?

I have this VB below which will look at exact values and delete those rows. But I need the reverse which is NOT delete rows with those values but delete all other rows.

Sub test()
Dim rng As Range, Cell As Range, del As Range 'For Deleting ZERO'S & 1e+22

Set rng = Intersect(Range("Q:Q"), ActiveSheet.UsedRange)
For Each Cell In rng
If (Cell.Value) = "1E+17" _
Or (Cell.Value) = "100000000000000000" _
Or (Cell.Value) = "51.8" _
Or (Cell.Value) = "Inf" Then
If del Is Nothing Then
Set del = Cell
Else: Set del = Union(del, Cell)
End If
End If
Next Cell
On Error Resume Next
del.EntireRow.Delete

End Sub

Any happy would be appreciated. I looked all over web and can't find anything to help me.

Thanks,
Weenie

4 replies

rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
769
Jul 23, 2011 at 01:17 AM
change
Or (Cell.Value) = "Inf" Then
If del Is Nothing Then _

tp

Or (Cell.Value) = "Inf" Then
else
If del Is Nothing Then
0
Thanks it works deleting those rows. But it deletes my header row 1 which I don't want it to. So, what would I add in code to prevent from deleting header row?

Thanks,
weenie
0
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
769
Jul 23, 2011 at 11:06 AM
Change
Set rng = Intersect(Range("Q:Q"), ActiveSheet.UsedRange)
to
Set rng = Intersect(Range("Q2:Q" & Rows.count), ActiveSheet.UsedRange)

Presumption is that row header is on row 1 and you want to check for deletion from row 2 on ward
0
Thank you. It works like a charm!

Weenie
0
Sorry to bug but I am bad with counters. I'm thinking I have to use a counter of some sort because When I run the above code (corrected one) it hangs up. Iam bring in new files constantly and will have to scan again and agin to delete these new data not meeting requirement. My sheet can be large as 900,000 rows when looking for the values.

Thanks,
Weenie
0
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
769
Jul 24, 2011 at 11:48 AM
I would suggest a different approach. Filter the values that you want to have. Then copy all the visible rows to a new sheet .
0
I did just that and seems to not crash on me.

Thanks.
0