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

[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

[Bob Phillips]

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)


"clmarquez" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>
>
> --
> clmarquez
> ------------------------------------------------------------------------
> clmarquez's Profile:
http://www.excelforum.com/member.php...o&userid=29386
> View this thread: http://www.excelforum.com/showthread...hreadid=490983
>

[clmarquez]

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!