VBA to delete rows between two specific strings in the same column

[DerekExcels]

Hey,

Hope everyone is well. Please see below example. I need a vba that deletes all rows between "Car" and "Bus" but I want all the rows with "Car" and "Bus" to remain and everything else to stay as is, i.e. "Bike" and "Jet".

With the below example, (Blanks) and "Truck" will be deleted with vba.

Column A

Bike
Bike
(Blank)
Car
Car
Car
(Blank)
Truck
(Blank)
Bus
Bus
Bus
(Blank)
Jet

Really appreciated,
Derek

 

[Peter_SSs]

Welcome to the MrExcel board!

Assuming at least one "car" and one "bus" in column A, try this with a copy of your workbook.

Sub Delete_Rows()
  Dim fr As Long, lr As Long

  fr = Columns("A").Find(What:="Car", LookAt:=xlWhole, SearchDirection:=xlPrevious, MatchCase:=False).Row + 1
  lr = Columns("A").Find(What:="Bus", LookAt:=xlWhole, SearchDirection:=xlNext, MatchCase:=False).Row - 1
  If lr >= fr Then Rows(fr).Resize(lr - fr + 1).Delete
End Sub

 

[DerekExcels]

Hey Peter

You saved my day and now my work can be a bit easier. Exactly the VBA I was looking for.

Really thankful.

 

[Peter_SSs]

You are welcome. Glad to help. Thanks for the follow-up.

 

[DPARDY]

Welcome to the MrExcel board!

Assuming at least one "car" and one "bus" in column A, try this with a copy of your workbook.

Sub Delete_Rows()
  Dim fr As Long, lr As Long

  fr = Columns("A").Find(What:="Car", LookAt:=xlWhole, SearchDirection:=xlPrevious, MatchCase:=False).Row + 1
  lr = Columns("A").Find(What:="Bus", LookAt:=xlWhole, SearchDirection:=xlNext, MatchCase:=False).Row - 1
  If lr >= fr Then Rows(fr).Resize(lr - fr + 1).Delete
End Sub

Peter_SSs:

I have the same issue, but I have a column full of "car" & "bus", how would I have it repeat that VBA for the entire A column?

Thanks, Dean

 

[Peter_SSs]

Peter_SSs:

I have the same issue, but I have a column full of "car" & "bus", how would I have it repeat that VBA for the entire A column?

Thanks, Dean

Could you give a small but representative set of sample data with 'before' and 'after' details (preferably with XL2BB)?

 

[DPARDY]

Peter_SSs,

Thank you for reaching out, but I was able to find something that worked for this problem. Again, Thank you.

 

[Peter_SSs]

I was able to find something that worked for this problem.

Cheers. Thanks for letting us know.