VBA: Search for two values and delete all rows between them

[xerhx]

I have two worksheets, Sheet1 and Sheet2. In Sheet1 i have two cells, B5 and B6 that contain two different values (User can change these). I'm trying to write a code that searches Column A in Sheet2 for the values in Sheet1 B5 and B6, and deletes the rows between them (including the cells that contain the values i search for).

I've been trying to do this for two days, and I can't seem to get it right.

Any suggestions?

Thanks.

Erik

 

[VoG]

Try

Sub DelData()
Dim F1 As Range, F2 As Range
With Sheets("Sheet2")
    Set F1 = .Columns("A").Find(what:=Sheets("Sheet1").Range("B5").Value, LookIn:=xlValues, lookat:=xlWhole)
    Set F2 = .Columns("A").Find(what:=Sheets("Sheet1").Range("B6").Value, LookIn:=xlValues, lookat:=xlWhole)
    If Not F1 Is Nothing And Not F2 Is Nothing Then .Rows(F1.Row & ":" & F2.Row).Delete
End With
End Sub

 

[xerhx]

Wow that did exactly what I needed, thanks for the super quick and accurate reply!

I almost had it right, the If Not F1 is Nothing did it. Thank you so much, you're a lifesaver!

Now, if it does find the value in column A, what would I add to make it display a YesNo msgbox that asks if the user wants to delete the data or not? That would make it complete..

 

[VoG]

Try

Sub DelData()
Dim F1 As Range, F2 As Range
With Sheets("Sheet2")
    Set F1 = .Columns("A").Find(what:=Sheets("Sheet1").Range("B5").Value, LookIn:=xlValues, lookat:=xlWhole)
    Set F2 = .Columns("A").Find(what:=Sheets("Sheet1").Range("B6").Value, LookIn:=xlValues, lookat:=xlWhole)
    If Not F1 Is Nothing And Not F2 Is Nothing Then
        If MsgBox("Are you sure that you want to delete rows " & IIf(F1.Row > F2.Row, F2.Row, F1.Row) & " to " & IIf(F1.Row < F2.Row, F2.Row, F1.Row), vbQuestion + vbYesNo) = vbYes Then .Rows(F1.Row & ":" & F2.Row).Delete
    End If
End With
End Sub

 

[xerhx]

Great!!! Thank you so much.

 

[VoG]

If you click No then nothing happens. But it would be

Sub DelData()
Dim F1 As Range, F2 As Range
With Sheets("Sheet2")
    Set F1 = .Columns("A").Find(what:=Sheets("Sheet1").Range("B5").Value, LookIn:=xlValues, lookat:=xlWhole)
    Set F2 = .Columns("A").Find(what:=Sheets("Sheet1").Range("B6").Value, LookIn:=xlValues, lookat:=xlWhole)
    If Not F1 Is Nothing And Not F2 Is Nothing Then
        If MsgBox("Are you sure that you want to delete rows " & IIf(F1.Row > F2.Row, F2.Row, F1.Row) & " to " & IIf(F1.Row < F2.Row, F2.Row, F1.Row), vbQuestion + vbYesNo) = vbYes Then
            .Rows(F1.Row & ":" & F2.Row).Delete
        Else
            Exit Sub
        End If
    End If
End With
End Sub

 

[xerhx]

This is what I have now..

Attached code:

    ' Update status
    Range("D6").Value = ("Checking for duplicates...")
    Application.Wait (Now + TimeValue("0:00:01"))
    
    
    'Search for duplicate campaign, and delete it if desired
    Dim F1 As Range, F2 As Range
    With Sheets("Campaign History")
        Set F1 = .Columns("B").Find(what:=Sheets("Add Campaign").Range("B14").Value, LookIn:=xlValues, lookat:=xlWhole)
        Set F2 = .Columns("B").Find(what:=Sheets("Add Campaign").Range("C14").Value, LookIn:=xlValues, lookat:=xlWhole)
    If Not F1 Is Nothing And Not F2 Is Nothing Then
        If MsgBox("Campaign already exists in History (Rows " & IIf(F1.Row > F2.Row, F2.Row, F1.Row) & " to " & IIf(F1.Row < F2.Row, F2.Row, F1.Row) & "). Do you want to delete the already saved campaign?", vbQuestion + vbYesNo) = vbYes Then
        .Rows(F1.Row & ":" & F2.Row).Delete
        GoTo PleaseResume:
    Else
        GoTo PleaseCancel:
    End If
    

    ' Tells the macro to stop if the user clicked "NO" in the previous MsgBox
PleaseCancel:
    End If
    End With
    Exit Sub

    ' Tells the macro to continue from here if the user clicked "YES" in the previous MsgBox
PleaseResume:

        
    ' Update status
    Range("D6").Value = ("Saving to history...")

I'm trying to tell the macro to go to delete the rows and goto PleaseResume: if the user has clicked YES, and goto PleaseCancel: if the user has clicked NO.

The problem is that if the code doesn't find the values F1 or F2 in my 2nd sheet, then it just quits as well.. How to make it just goto PleaseResume: in that case?

Sorry for being such a newbie, this turned out to be way harder than I thought at first

 

[VoG]

Sorry but this is spaghetti code http://en.wikipedia.org/wiki/Spaghetti_code

What do you actually want to do?

 

[xerhx]

Haha just learned a new term there.. OK in plain text:

What we've accomplished so far:
- Searching for values in two cells from Sheet1 in Sheet2
- Deleting the rows between those two values in Sheet2 (Incl. the actual value rows).
- Creating a msgbox that asks the user if he really wants to delete the rows, and ends the macro if "NO" is clicked.

What I'm still looking to accomplish:
- If "YES" is clicked, Then delete the rows, and run another macro.
- If "NO" is clicked, just end the sub without running the other macro.

Just disregard my previous post, I feel kind of embarrassed now

 

[VoG]

Try something like

Sub DelData()
Dim F1 As Range, F2 As Range
With Sheets("Sheet2")
    Set F1 = .Columns("A").Find(what:=Sheets("Sheet1").Range("B5").Value, LookIn:=xlValues, lookat:=xlWhole)
    Set F2 = .Columns("A").Find(what:=Sheets("Sheet1").Range("B6").Value, LookIn:=xlValues, lookat:=xlWhole)
    If Not F1 Is Nothing And Not F2 Is Nothing Then
        If MsgBox("Are you sure that you want to delete rows " & IIf(F1.Row > F2.Row, F2.Row, F1.Row) & " to " & IIf(F1.Row < F2.Row, F2.Row, F1.Row), vbQuestion + vbYesNo) = vbYes Then
            .Rows(F1.Row & ":" & F2.Row).Delete
            Call MyMacro
        Else
            Exit Sub
        End If
    End If
End With
End Sub

Change MyMacro to the name of the macro that you want to run.