Delete rows in VBA

Question

I'm writing a VBA code in Excel 2013. I need to filter a sheet and leave certain rows (lets say filter column B and leave the rows that contains "Apple" in that column) - But, I want to delete all rows except those with the "Apple".

So my question is - is there a smart way to do that in a VBA code, and if so then how? or should I filter everything except "Apple", Copy-Paste the lines remaining to a different sheet and then work on the new sheet?

Thank You!

look into my answer here: [Delete Hidden/Invisible Rows after Autofilter Excel VBA](http://stackoverflow.com/questions/22275423/delete-hidden-invisible-rows-after-autofilter-excel-vba) — Dmitry Pavliv, Apr 13 '14 at 12:20

score 0 · Answer 1 · answered Apr 13 '14 at 12:25

Without an AutoFilter:

Sub RowKiller2()
    Dim N As Long, rDel As Range, cl As String
    Dim kv As Variant, r As Range, rBig As Range
    cl = "B"
    kv = "Apple"
    N = Cells(Rows.Count, cl).End(xlUp).Row
    Set rBig = Range(cl & 1 & ":" & cl & N)
    For Each r In rBig
    If r.Value <> kv Then
        If rDel Is Nothing Then
            Set rDel = r
        Else
            Set rDel = Union(rDel, r)
        End If
    End If
    Next r

    If Not rDel Is Nothing Then
        rDel.EntireRow.Delete
    End If

End Sub

Delete rows in VBA

1 Answers1