CODEWITHSUNDEEP
vbaexcel
joepd
joepd

Reputation: 49

Deleting rows in Excel using VBA

I need to delete rows that have a certain value in a certain column. The number of rows is variable. I tried to do this with the following VBA code:

Sub DeleteRows()

   Dim i, LastRow As Integer
   LastRow = ActiveSheet.Cells(Sheets(1).Rows.Count, 1).End(xlUp).row

   For i = 2 to LastRow

      If Cells(i, 1).Value <> "certain value" Then

         Rows(i).Delete

      End If

   Next i

End Sub

LastRow does contain the correct value. When I run this code, nothing happens. I'm new in VBA, I hope someone can push me in the right direction. Thanks in advance.

Upvotes: 0

Views: 199

Answers (1)

h2so4
h2so4

Reputation: 1577

try this

Sub DeleteRows()

   Dim i as long, LastRow As long
   with activesheet
   LastRow = .Cells(.Rows.Count, 1).End(xlUp).row

   For i =  LastRow to 2 step -1

      If .Cells(i, 1).Value <> "certain value" Then

         .Rows(i).Delete

      End If

   Next i
   End With
End Sub

Upvotes: 2

Related Questions