Deleting rows in Excel using VBA

Question

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.

stackoverflow.com/questions/43895629/… loop the other way

Nathan_Sav
– Nathan_Sav

2017-05-10 14:54:44 +00:00
Commented May 10, 2017 at 14:54 — Nathan_Sav
– Nathan_Sav, Commented May 10, 2017 at 14:54

h2so4 · Accepted Answer · 2017-05-11 11:44:07Z

2

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

edited May 11, 2017 at 11:44

answered May 10, 2017 at 14:55

h2so4

1,5771 gold badge10 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Scott Craner Over a year ago

I missed this also, change the declaration line to Dim i as Long, LastRow As Long If not i will be variant and LastRow will overflow if the rows are large.

Collectives™ on Stack Overflow

Deleting rows in Excel using VBA

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related