Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
delete a cell if it contains specific word
Im trying to write a formula were i can get the programe to check the file
and if a cell contains 'agency' i want it to delete the cell and shift it left. the reason for the formula is that the file will be in a differnt order/size each time, and cannot just do this manually. Thanks |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
delete a cell if it contains specific word
Enter and run this macro:
Sub Macro1() Dim i, j As Long Dim s, ss As String ss = "agency" For i = 10 To 1 Step -1 For j = 10 To 1 Step -1 s = Cells(j, i).Value If Not (InStr(s, ss) = 0) Then Cells(j, i).Delete Shift:=xlToLeft End If Next Next End Sub As coded it covers 10 rows by 10 columns. Expand it to suit your needs. -- Gary's Student "Tim" wrote: Im trying to write a formula were i can get the programe to check the file and if a cell contains 'agency' i want it to delete the cell and shift it left. the reason for the formula is that the file will be in a differnt order/size each time, and cannot just do this manually. Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
show name when a cell has specific word | Excel Discussion (Misc queries) | |||
Pasting Word table cell with paragraph markers into single Excel c | Excel Discussion (Misc queries) | |||
Automatically Put a Value in a specific cell. | Excel Discussion (Misc queries) | |||
how do I highlite text within a cell (specific characters) | Excel Discussion (Misc queries) | |||
Copy cell format to cell on another worksht and update automatical | Excel Worksheet Functions |