Microsoft Office Tutorials and References
In Depth Information
Deleting Rows Based on Criteria
Option Explicit¶
' * * * * *¶
Sub DeleteRows()¶
'Variable declarations¶
'The range to be used¶
Dim Rng As Range¶
'The column with the condition¶
Dim WhichColumn As Long¶
'The condition to be matched¶
Dim TheCondition As Variant¶
'Change the following variables as desired¶
'Set the range by hard coding¶
Set Rng = Range("A1:C20")¶
'Uncomment the next line if you want to use the selected¶
'range as the variable¶
'Set Rng = ActiveWindow.RangeSelection¶
'Hard coded variable definition¶
'First column of the range has the condition¶
WhichColumn = 1 ¶
'Set variable via user input¶
'WhichColumn = Range("B24").Value¶
'Hard coded variable definitions¶
TheCondition = 0 'Delete rows where the cell equals 0¶
'TheCondition = ">0" 'Delete rows where the cell is greater than 0¶
'TheCondition = "" 'Delete rows where the cell is empty¶
'Set variable via user input¶
'TheCondition = Range("B25").Value¶
'Turn off screen updating¶
Application.ScreenUpdating = False¶
'In order to use autofilter, make sure it is not already used¶
If Rng.Worksheet.AutoFilterMode = True Then¶
Rng.Worksheet.AutoFilterMode = False¶
End If¶
'Filter the Rng¶
Rng.AutoFilter Field:=WhichColumn, Criteria1:=TheCondition¶
'Look for visible rows in Rng (start from row 2 of Rng, not row 1)¶
With Rng¶
'turn off errors in case there are no visible cells¶
On Error Resume Next¶
.Offset(1).Resize(.Rows.Count - 1).SpecialCells( _¶
xlCellTypeVisible).Delete Shift:=xlShiftUp¶
On Error GoTo 0¶
'Turn off autofilter again¶
.Worksheet.AutoFilterMode = False¶
End With¶
'Restore screen updating¶
Application.ScreenUpdating = True¶
End Sub¶
Search JabSto ::

Custom Search