Excel VBA Delete Rows In between 2 Values

[Plokimu77]

Good Afternoon,

Is it possible for VBA to go down column A and look for the word Mike and delete all the rows until it finds George in the same column?

Thank you for the help.

Raw Data:

Column A

Cat

Dog

Mike

Yellow

Blue

Orange

George

Example Final Result:

Column A

Cat

Dog

Mike

George

 

[JoeMo]

This assumes the names start in A1, change to suit:

Sub DeleteMikeUntilGeorge()
'Assumes names begin in A1
Dim Lrw As Long, V As Variant, i As Long, Start As Range, Nd As Range
Lrw = Cells(Rows.Count, "A").End(xlUp).Row
V = Range("A1:A" & Lrw).Value  'change A1 to suit
For i = LBound(V, 1) To UBound(V, 1)
    If V(i, 1) = "Mike" Then
        Set Start = Range("A" & i)
    ElseIf V(i, 1) = "George" Then
        If Start Is Nothing Then Exit Sub
        Set Nd = Range("A" & i)
    End If
Next i
If Nd Is Nothing Then
    Start.Offset(1, 0).Resize(Lrw - Start.Row + 1).Delete shift:=xlUp
ElseIf Nd.Row > Start.Row Then
    Range(Start, Nd).Offset(1, 0).Resize(Range(Start, Nd).Rows.Count - 2).Delete shift:=xlUp
End If
End Sub

 

[Plokimu77]

Excellent.

Thank you very much.

It is greatly appreciated.

 

[Plokimu77]

JoeMo,

I have one request please.

Can the code delete the entire rows in between? Not just the ones in Column A.

Thanks


I apologies for not providing you more detail information.

I have values in the rest of those rows that i do not need.

Thanks once again.

A	B	C	D	E	F
Cat	777​	773​	168​	374​	446​
Dog	638​	587​	637​	390​	703​
Mike	502​	976​	403​	27​	362​
Yellow	834​	65​	978​	736​	787​
Blue	161​	183​	288​	908​	807​
Orange	483​	650​	393​	504​	527​
George	995​	176​	535​	915​	139​

 

[JoeMo]

Excellent.

Thank you very much.

It is greatly appreciated.

You are welcome - thanks for the reply.

 

[JoeMo]

JoeMo,

I have one request please.

Can the code delete the entire rows in between? Not just the ones in Column A.

Thanks

​

Here you go:

Sub DeleteMikeUntilGeorge2()
'Assumes names begin in A1
Dim Lrw As Long, V As Variant, i As Long, Start As Range, Nd As Range
Lrw = Cells(Rows.Count, "A").End(xlUp).Row
V = Range("A1:A" & Lrw).Value  'change A1 to suit
For i = LBound(V, 1) To UBound(V, 1)
    If V(i, 1) = "Mike" Then
        Set Start = Range("A" & i)
    ElseIf V(i, 1) = "George" Then
        If Start Is Nothing Then Exit Sub
        Set Nd = Range("A" & i)
    End If
Next i
If Nd Is Nothing Then
    Start.Offset(1, 0).Resize(Lrw - Start.Row + 1).EntireRow.Delete
ElseIf Nd.Row > Start.Row Then
    Range(Start, Nd).Offset(1, 0).Resize(Range(Start, Nd).Rows.Count - 2).EntireRow.Delete
End If
End Sub

 

[Plokimu77]

Thank you!!

 

[JoeMo]

Thank you!!

You are welcome - thanks for the reply.