Visual Basic - Deleting rows in spreadsheet

March 2017




Issue


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 

Solution


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.

Related


Published by aakai1056.
This document, titled "Visual Basic - Deleting rows in spreadsheet," is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (ccm.net).