CODEWITHSUNDEEP
excelvba
Hayk
Hayk

Reputation: 47

Deleting Multiple Rows based on a Set Criteria

Sub Macro()
Dim i As Long
For i = Cells(Rows.Count, 14).End(xlUp).Row To 2 Step -1
    If Cells(i, 14).Value2 = "APPLE" Then
        Rows(i).Delete
    End If
Next i

Dim f As Long
For f = Cells(Rows.Count, 14).End(xlUp).Row To 2 Step -1
    If Cells(f, 14).Value2 = "NAME" Then
        Rows(f).Delete
    End If
Next f
End Sub

I have the above mentioned code to delete all the rows that have apple and name on them, If possible I would like excel to execute the code in one or two lines. Your help would be greatly appreciated!

Upvotes: 0

Views: 59

Answers (2)

JohnyL
JohnyL

Reputation: 7142

The fast way to delete rows is using AutoFilter:

Sub FastDelete()
    Dim rng As Range, rngVisible As Range
    '//Remove filter if any
    ActiveSheet.AutoFilterMode = False
    '// Get range of only one column (N)
    Set rng = Range(Cells(1, 14), Cells(Rows.Count, 14).End(xlUp))
    '// Field:=1 because filter has only one field
    rng.AutoFilter Field:=1, Criteria1:=Array("APPLE", "NAME"), Operator:=xlFilterValues
    '// Have error handling in case if no data is found
    On Error Resume Next
    With rng
        '// Use Offset and Resize to exclude header
        Set rngVisible = .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible)
    End With
    '// If rows were found (i.e. there's no error), delete them
    If Err = 0 Then rngVisible.EntireRow.Delete
    On Error GoTo 0
    '// Remove filter
    ActiveSheet.AutoFilterMode = False
End Sub

Upvotes: 1

Cyril
Cyril

Reputation: 6829

Dim i As Long
For i = Cells(Rows.Count, 14).End(xlUp).Row To 2 Step -1
    IF Cells(i, 14).Value2 = "APPLE" OR Cells(i, 14).Value2 = "NAME" THEN Rows(i).Delete
Next i

Upvotes: 1

Related Questions