VBA Code to Stop Running Current Code after It is complete

Question

I am trying to find the cells value "Details" and delete that row along with the 3 rows below it.

The code I current have works but once there are no longer any cells with "Details", I get the error box asking me to end the macro or debug.

How do I fix current code and eliminate the error message box from popping up.

My current code is below.

Dim StartRange As String
Dim EndRange As String
For Each Cell In ActiveSheet.Range("A1:A1000")
    Cells.Find(What:="Details").Select
    StartRange = ActiveCell.Address
    Selection.Offset(3, 75).Select
    EndRange = ActiveCell.Address
    ActiveSheet.Range(StartRange & ":" & EndRange).Select
    Selection.EntireRow.Delete
Next Cell

What is the error message exactly, and which line/instruction is the VBA editor highlighting when you click "debug"? — Mathieu Guindon, Sep 04 '18 at 19:46
That said, [this](https://stackoverflow.com/q/10714251/1188513) would be a relevant read. — Mathieu Guindon, Sep 04 '18 at 19:46
It's not clear what your code is doing. It's iterating over 1000 cells but searches for `Details` all over the sheet. Be aware, that `Find` searches for partial result. — JohnyL, Sep 04 '18 at 19:58

dwirony · Answer 1 · 2018-09-04T20:54:32.760

2

Try this - note, it's best to fully qualify Range with your Workbook and Worksheet name (i.e. Workbooks("MyWorkbook").Worksheets("MyWorksheet").Range().

Option Compare Text
Sub Test()

Dim i As Long

For i = 10000 To 1 Step -1
    If Range("B" & i).Value = "details" Then
        Range("B" & i & ":B" & i + 3).EntireRow.Delete
    ElseIf Range("V" & i).Value = "pending renewal" Then
        Range("V" & i & ":V" & i + 7).EntireRow.Delete
    End If
Next i

End Sub

edited Sep 04 '18 at 20:54

answered Sep 04 '18 at 19:58

dwirony

5,487
3
21
43

1

`Range(Cells(i, "A"), Cells(i - 3, "A"))` is lookin' better :) – JohnyL Sep 04 '18 at 19:59
1

@JohnyL Either of them would look better with fully qualified calls to `Range` or `Cells`. – Comintern Sep 04 '18 at 20:00
2

@Comintern OP never provided any workbook or worksheet names, so an unqualified `Range` might as well be just as effective as his use of `ActiveSheet` - I think it's beyond the point until he provides more details. – dwirony Sep 04 '18 at 20:02
I guess `i + 3` - not `i - 3` – JohnyL Sep 04 '18 at 20:02
@JohnyL Stepping backwards, but I did change it to `Range("A" & i - 3 & ":A" & i).EntireRow.Delete` ;) – dwirony Sep 04 '18 at 20:03
OP says `3 rows` below! :) That's why you need `+` instead of `-` :) – JohnyL Sep 04 '18 at 20:06
@Comintern *another poor practice* The qualifying ain't relevant in OP's context. That's why I asked you don't start with this... – JohnyL Sep 04 '18 at 20:10
Thanks for the code above! It does exactly what I needed it to do. I would like to do the same thing based on a few other criteria found in different columns, but when i replicate the code and change it to find the next criteria, only one of the codes works. The code i have written is below. Also, i am relatively new at using VBA so sorry if i am asking simple/dumb questions – Mac MacDonald Sep 04 '18 at 20:33
Dim i As Long For i = 10000 To 1 Step -1 If Range("B" & i).Value = "Details" Then Range("B" & i & ":B" & i + 3).EntireRow.Delete End If Next i Dim j As Long For j = 10000 To 1 Step -1 If Range("V" & j).Value = "Pending renewal" Then Range("V" & j & ":V" & j + 7).EntireRow.Delete End If Next j – Mac MacDonald Sep 04 '18 at 20:33
@MacMacDonald Looks fine to me - see my edited answer above. No need for a new variable or another loop. – dwirony Sep 04 '18 at 20:37
@dwirony Thanks. when i inputed the edited code, it is still only removing the rows with "Pending renewal" and not those with "Details" – Mac MacDonald Sep 04 '18 at 20:43
@MacMacDonald Is "Details" in column A or B? In your modified code you changed column A to B - also do you have 10,000 rows or only 1000? – dwirony Sep 04 '18 at 20:45
@dwirony "Details" is in column B, and there are 10,000 rows. I mistakenly coded it wrong in my first post. – Mac MacDonald Sep 04 '18 at 20:47
@MacMacDonald Should be working... are you sure it's just "Details" in the cell? No trailing/leading white space? You said it was working fine the first time but now it's not? I can't reproduce your issue. I've edited the code to use `InStr` just in case... otherwise, double check those columns. – dwirony Sep 04 '18 at 20:51
@dwirony I figured out what my issue was. The D in Details was not capitalized so the code was not finding it. Thanks for all your help!! – Mac MacDonald Sep 04 '18 at 20:52
@MacMacDonald Ah, in that case you can even put `Option Compare Text` above `Sub Test()` and it will ignore all cases :) – dwirony Sep 04 '18 at 20:54

VBA Code to Stop Running Current Code after It is complete

1 Answers1