Excel VBA Delete Rows In between 2 Values

Plokimu77

Board Regular
Joined
Oct 1, 2014
Messages
122
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
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,389
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
This assumes the names start in A1, change to suit:
VBA Code:
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

Board Regular
Joined
Oct 1, 2014
Messages
122
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.


ABCDEF
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

MrExcel MVP
Joined
May 26, 2009
Messages
17,389
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

Excellent.

Thank you very much.

It is greatly appreciated.
You are welcome - thanks for the reply.
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,389
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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:
VBA Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,318
Messages
5,600,921
Members
414,416
Latest member
Nobu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top