Find Text and Delete in VBA

[knaimi]

Please see Excel spreadsheet below:

Last	First
Smith	John
Smith	John
Smith	John
Smith	John
Student:	Smith	John
Smith	John
Smith	John
Smith	John
Smith	John
Smith	John
Smith	John
Student:	Smith	John
Smith	John
Smith	John
Student:	Smith	John
Smith	John
Smith	John
Student:	Smith	John
Smith	John
Smith	John

I'm trying to create a code to find and delete all the cells that contain the text"Student:", and shift the cells left. This text can appear more or less depending on how many students logged in inside vs. outside the school.

Thanks,

knaimi

 

[Fluff]

How about

Sub knaimi()
   With Range("A2", Range("A" & Rows.Count).End(xlUp))
      .Replace "Student:", True, xlWhole, , False, , False, False
      On Error Resume Next
      .SpecialCells(xlConstants, xlLogical).Delete xlShiftToLeft
      On Error GoTo 0
   End With
End Sub

 

[knaimi]

Thanks, Fluff!. It worked

 

[Fluff]

You're welcome & thanks for the feedback.