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
Change the following:
Or (Cell.Value) = "Inf" Then If del Is Nothing Then _ tp Or (Cell.Value) = "Inf" Then else If del Is Nothing Then
Thanks to rizvisa1 for this tip.