CODEWITHSUNDEEP
vbaexcelloops
Mauro
Mauro

Reputation: 479

How to delete complete rows automatically under one condition on vba?

I'm a beginner with VBA. I have the following simple loop code in order to delete from a data base the rows which have a "na" value on the column 9 cell:

Dim n As Integer
n = 500

  For i = 1 To n
  If Cells(i, 9).Value = "n.a." Then
    Rows("i:i").Select
    Selection.Delete Shift:=xlUp

   End If
  Next i

End Sub

But I get the 1004 error, could anyone help me please?

Upvotes: 2

Views: 1221

Answers (3)

robinCTS
robinCTS

Reputation: 5886

The fastest way to delete these values is using AutoFilters. No need for loops.

Option Explicit

Public Sub AutoFilterDelete()

  With Range("I1:I500")
    .AutoFilter Field:=1, Criteria1:="n.a"
    .Resize(WorksheetFunction.Match("*", .Cells, -1)).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    .Parent.AutoFilterMode = False
  End With

End Sub

Upvotes: 1

Subodh Tiwari sktneer
Subodh Tiwari sktneer

Reputation: 9976

You simply need this...

Dim n As Integer
n = 500
  For i = n To 1 Step -1
    If Cells(i, 9).Value = "n.a." Then Rows(i).Delete
  Next i

Edit:

Or more correctly this to make it dynamic...

Application.ScreenUpdating = False
Dim n As Long
n = Cells(Rows.Count, 9).End(xlUp).Row
  For i = n To 1 Step -1
    If Cells(i, 9).Value = "n.a." Then Rows(i).Delete
  Next i
Application.ScreenUpdating = True

Upvotes: 3

Vityata
Vityata

Reputation: 43585

Option Explicit

Public Sub TestMe()

    Dim n As Integer
    n = 500

    For i = n To 1 Step -1
        If Cells(i, 9).value = "n.a." Then
            rows(i & ":" & i).Select
            Selection.Delete Shift:=xlUp
        End If
    Next i

End Sub

Whenever you are deleting rows, start doing it from the biggest to the smallest. Later you can google how to avoid Select and how to use Union in order to do the deletion in one step. Finally, you can replace Integer to Long.

Upvotes: 3

Related Questions