0

The code below is called from another sub. Everything in that sub works as expected. However, when this sub is initiated, it does not delete the rows. The desired result is to delete all rows that contains "After Dispute For SBU" in column J, When I go into the specific worksheet, the entire used range is selected, but that is where the sub seems to stop. I do not get any error messages. There is another called procedure listed after this one that does work. I cannot see anything wrong with this sub are any cause for it not to complete.

Sub Remove_After_Dispute()

    Application.ScreenUpdating = False

    With ActiveWorkbook.Worksheets("MIM Data")

        Dim DelAftDis As Long
        For DelAftDis = Cells(Rows.Count, 10).End(xlUp).row To 2 Step -1

            With Cells(DelAftDis, 10)
                If .Value = "After Dispute For SBU" Then
                    Rows(DelAftDis).EntireRow.Delete
                End If
            End With

        Next DelAftDis

    End With

    Application.ScreenUpdating = True

End Sub
Community
  • 1
  • 1
Iron Man
  • 742
  • 1
  • 12
  • 26
  • 3
    you use the With Block but do not tie the Range Objects to it. For every `Cells(...` or `Rows(...` put a `.` in front of them. So `.Cells(.Rows.Count, 10).End(xlUp).row` – Scott Craner Mar 31 '16 at 23:46
  • Works perfectly for me. I'm guessing you have another sheet active. – Comintern Mar 31 '16 at 23:47
  • 1
    What @ScottCraner said. Note that with the nested `With` blocks, you'll need to either use `.Parent.Rows(DelAftDis).EntireRow.Delete` or `.Rows(1).EntireRow.Delete`. – Comintern Mar 31 '16 at 23:51
  • 3
    @Comintern or just `.EntireRow.Delete` – Scott Craner Mar 31 '16 at 23:54
  • 2
    @ScottCraner - Yes, or that. *face-palm*. – Comintern Mar 31 '16 at 23:55
  • @ScottCraner So instead of `Rows(DelAftDis).EntireRow.Delete` I should just have `.EntireRow.Delete`? And I did use your first suggestion by adding the `.`. But that did not work. – Iron Man Mar 31 '16 at 23:56
  • 1
    @IronMan - A cell only has one row - if you use anything other than 1 for an index the Range it returns obviously won't contain that row. – Comintern Mar 31 '16 at 23:57
  • 1
    @IronMan Yes with it in you With Block it is like saying `.Cells(DelAftDis, 10).EntireRow.Delete` make sure you put a `.` on the nested with; `With .Cells(DelAftDis, 10)` – Scott Craner Mar 31 '16 at 23:57
  • @ScottCraner Thanks Scott, I am leaving for the day and will try this tomorrow. Thanks for your help. – Iron Man Mar 31 '16 at 23:59
  • @Comintern Thank you for your suggestions also. Appreciate it. Will keep you posted on my progress tomorrow. – Iron Man Apr 01 '16 at 00:00
  • 1
    Thanks @ScottCraner! I tested this: `With ActiveWorkbook.Worksheets("MIM Data")` `Dim DelAftDis As Long` `For DelAftDis = .Cells(Rows.Count, 10).End(xlUp).row To 2 Step -1` `With .Cells(DelAftDis, 10)` `If .Value = "After Dispute For SBU" Then` `.EntireRow.Delete` `End If` `End With` `Next DelAftDis` `End With` `Application.ScreenUpdating = True` this morning and it worked perfectly. – Iron Man Apr 01 '16 at 14:46
  • 1
    @chrisneilsen When I did my search on this, I did not find your "duplicate" post in the results. For someone new to VBA, they are not going to see how these are related and are going to be asking questions based on the simplest terms. I think this question can stand. Also, I think that both answers are valid, but the answer provided by Scott works best for my needs. So there are slight alterations between the two that I feel make them questions that each stand on their own. – Iron Man Apr 01 '16 at 15:04
  • @IronMan that you have had the same problem (unqualified references) in a different context does make this less of a duplicate. That you didn't find that answer is a _good_ reason to close as dup: others may find your Q as a signpost to their answer. – chris neilsen Apr 01 '16 at 23:52
  • @chrisneilsen please don't get me wrong. There are no hard feelings here. Just want to make sure other, less experienced users find answers they need on this site. Your comment above regarding the fact that I did not find your answer allows me to agree with you that closing this will help users find more possible references. Thanks. – Iron Man Apr 02 '16 at 01:55
  • 1
    @IronMan I'm glad you got your answer, and glad too that you appreciate how this site works. The aim is always to create lasting value for future users. No hard feelings on my part too, my votes are on the Q, not the person – chris neilsen Apr 02 '16 at 03:55

0 Answers0