Finding & deleting cells that do not contain a certain text string

  • Thread starter Thread starter clmarquez
  • Start date Start date
C

clmarquez

Hello,

I'm brand new to VB for Excel, but I am trying...

I have a sheet with multiple columns of data. In column A, I have some
names that have the format "lastname, first initial". Some cells within
column A do not have the comma, and these are the ones that I would like
to delete. I only want to delete the cell (and shift others up), so
that the other columns are not affected.

I found some older posts that had asked a similar problem, but they
dealt with deleting the entire row. When I would change this to just
the selection, the macro would get hung up on that line. Any help
would be greatly appreciated.

Thanks,
Kez
 
Sub Test()
Dim iLastRow As Long
Dim i As Long

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = iLastRow To 1 Step -1
If Cells(i, "A").Value Like "*,*" Then
Cells(i, "A").Delete Shift:=xlUp
End If
Next i

End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)
 
Worked good, except it was deleting the cells that I wanted to keep!
So, I added a "Not" after your "If", and it solved the problem.

Thanks again for the quick reply and solution
 
Back
Top