0

I am new to macros and I am looking to delete rows containing the text "Not Applicable" in Column D. I have attached my code below. On compiling, I am getting the error "Application or Object Defined Error". Please let me know what's the right way. The first part of the code is a filtering function which was working fine. I am adding this row delete part and thats when the error is coming. 

 Sub Advanced_Filtering()
    If Worksheets("Engagement Sheet").Range("B2") = 2 Then
    Range("C2") = Null
    ElseIf Worksheets("Engagement Sheet").Range("B2") = 1 Then
    Range("C2") = 1
    End If
    Range("A7:G1500").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range("A1:G2"), CopyToRange:=Sheets("Sheet3").Range("L1:R1")


BeginRow = 5
    EndRow = 120
    ChkCol = 4
    For Rowcnt = EndRow To BeginRow Step -1
        If ThisWorkbook.ActiveSheet.Cells(Rowcnt, ChkCol).Value = "Not Applicable" Then
           ThisWorkbook.ActiveSheet.Cells(Rowcnt, ChkCol).EntireRow.Delete

        End If
    Next Rowcnt

ActiveSheet.Copy

End Sub
Seb12345
  • 3
  • 4
  • 3
    You need to loop backwards if you delete rows `For Rowcnt = EndRow To BeginRow Step -1`. Otherwise the row counting changes everytime you delete a row and your `For` counter `Rowcnt` counts wrong. – Pᴇʜ Jan 31 '20 at 12:59
  • Does this answer your question? [Excel VBA deleting rows in a for loop misses rows](https://stackoverflow.com/questions/43454139/excel-vba-deleting-rows-in-a-for-loop-misses-rows) – Pᴇʜ Jan 31 '20 at 13:01
  • See [this answer](https://stackoverflow.com/a/59975507/9245853) (or other similar ones) for a fast approach using `Union` and deleting at the end. – BigBen Jan 31 '20 at 13:06
  • Thanks for that. Didnt think about that. You also mentioned that Rowcnt counts wrong, what should be the edit for that ? Does the "Step -1" ensure that Next rowcnt=rowcnt-1 or do we need to mention something separate. – Seb12345 Jan 31 '20 at 13:06
  • Or just use a filter. – BigBen Jan 31 '20 at 13:06
  • I have edited the code for rowcnt to count backword, but the error is still there. I was not able to understand the answers you guys suggested. Apologies my coding and macro experience is very low. – Seb12345 Jan 31 '20 at 13:22
  • You've got an extra in `.Cells(Rowcnt, ChkCol)` in `ThisWorkbook.ActiveSheet.Cells(Rowcnt, ChkCol).Cells(Rowcnt, ChkCol).EntireRow.Delete` – BigBen Jan 31 '20 at 13:27
  • Yea just noticed that, I have removed it. But the error still comes up. Am I using the cells function wrongly ? – Seb12345 Jan 31 '20 at 13:34
  • In which line do you get the error? – Pᴇʜ Jan 31 '20 at 13:35
  • Doesnt mention the line. Just Run-time error '1004'; "Application defined or object defined error" – Seb12345 Jan 31 '20 at 13:36
  • but there should be a line highlighted in yellow if you click debug. – Pᴇʜ Jan 31 '20 at 13:39
  • I am not getting highlighted in yellow. – Seb12345 Jan 31 '20 at 13:45

0 Answers0