Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding & deleting cells that do not contain a certain text string
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding & deleting cells that do not contain a certain text string
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding & deleting cells that do not contain a certain text string
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 -- clmarque ----------------------------------------------------------------------- clmarquez's Profile: http://www.excelforum.com/member.php...fo&userid=2938 View this thread: http://www.excelforum.com/showthread.php?threadid=49098 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Finding a charcter in a text string | Excel Worksheet Functions | |||
Finding specific text in a string | Excel Worksheet Functions | |||
Finding a text string w/in a Cell | Excel Discussion (Misc queries) | |||
Finding text in a string | Excel Programming | |||
Finding & Deleting Spaces from Cells | Excel Programming |