2

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?

Jason Aller
  • 3,541
  • 28
  • 38
  • 38
Mauro
  • 477
  • 1
  • 9
  • 22
  • 1
    It is better to avoid [Select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba), and refer to this question to look on the [performance of deleting](https://stackoverflow.com/questions/46163851/excel-vba-performance-issues-comparing-row-to-column-delete) and [Improving a Loop to delete rows in excel faster](https://stackoverflow.com/q/46077673/7690982). Or [Performance - 1 million rows - Delete rows](https://stackoverflow.com/q/30959315/7690982). Because if the Worksheet is too large it can become slow. – danieltakeshi Sep 14 '17 at 11:51

3 Answers3

3
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.

Vityata
  • 42,633
  • 8
  • 55
  • 100
  • I still get the same error althought I have implemented your modifications, the error text is: "error defined by the application or the object", do you know why is it coming up? Thanks for your help – Mauro Sep 14 '17 at 11:44
  • @Mauro - try again, `rows(i & ":" & i).Select` was wrong as well – Vityata Sep 14 '17 at 11:45
  • I still get the same error, I don't understand why because the code seems to be correct. – Mauro Sep 14 '17 at 11:51
  • 1
    Now I get another error: "an object is needed", could it be because I have to actívate first the sheet where I'm working? – Mauro Sep 14 '17 at 11:54
  • @Mauro - yes if you are using select you have to take care of the activated sheets as well. – Vityata Sep 14 '17 at 11:54
  • Thank you very much I validate your answer as you were first. – Mauro Sep 14 '17 at 12:00
  • Cool, thanks @Mauro. Once you have time, investigate the 3 ideas in the answer and try to implement the in your code. – Vityata Sep 14 '17 at 12:01
3

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
Subodh Tiwari sktneer
  • 9,906
  • 2
  • 18
  • 22
  • 1
    [The fastest way would be to select all the rows first then delete them at once using Range.Delete. It's the delete operation that takes the most time. – John Wu Sep 6 at 14:22](https://stackoverflow.com/a/46077868/7690982) So it is better to not delet each row and create a non contiguous range or filter for the value to make it faster. Your code works and received thumbs up, just a comment to improve for other people looking in the future. – danieltakeshi Sep 14 '17 at 11:58
  • Thank you very much – Mauro Sep 14 '17 at 12:01
1

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
robinCTS
  • 5,746
  • 14
  • 30
  • 37