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
– Mathieu Guindon, Commented Sep 4, 2018 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
– JohnyL, Commented Sep 4, 2018 at 19:58

dwirony · Accepted Answer · 2018-09-04 20:54:32Z

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 4, 2018 at 20:54

answered Sep 4, 2018 at 19:58

dwirony

5,4604 gold badges26 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

16 Comments

JohnyL Over a year ago

Range(Cells(i, "A"), Cells(i - 3, "A")) is lookin' better :)

Comintern Over a year ago

@JohnyL Either of them would look better with fully qualified calls to Range or Cells.

dwirony Over a year ago

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

JohnyL Over a year ago

I guess i + 3 - not i - 3

dwirony Over a year ago

@JohnyL Stepping backwards, but I did change it to Range("A" & i - 3 & ":A" & i).EntireRow.Delete ;)

|

Collectives™ on Stack Overflow

VBA Code to Stop Running Current Code after It is complete

1 Answer 1

16 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

16 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related