Visual Basic - Deleting rows in spreadsheet

October 2016


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 

End Sub 


Change the following:
Or (Cell.Value) = "Inf" Then 
If del Is Nothing Then _ 


Or (Cell.Value) = "Inf" Then 
If del Is Nothing Then

Thanks to rizvisa1 for this tip.

Related :

This document entitled « Visual Basic - Deleting rows in spreadsheet » from CCM ( is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.