PDA

View Full Version : if find matched data, delete the column data



parscon
04-20-2013, 01:14 PM
I have a data on column A about 66000 row , Now i need a vba code that if find a matched data for example "apple" cleran the contect of that cloumn

For example :

A1: SomeDATA
A2: DATASOME
A3: apple
A4: DATA

When run the VBA:

A1: SomeDATA
A2: DATASOME
A3:
A4: DATA

A3 will be clean . and important thing i do not want delet the row just clean the column .

THank you

parscon
04-20-2013, 01:31 PM
i found this , but I need check all row of column A not 1 to 3000


Sub Macro1()
For Row = 1 To 3000
S = Range("A" & Row).Cells.Value
Pos = InStr(S, "apple")
If Pos > 0 Then Range("A" & Row).Cells.Value = ClearContents
Next
End Sub

mdmackillop
04-21-2013, 04:30 AM
Better to use Find than test every cell
Sub Test()

Dim c As Range, FirstAddress As String
With Worksheets(1).Range("A:A")
Set c = .Find("apple", LookIn:=xlValues, LookAt:=xlWhole)
If Not c Is Nothing Then
FirstAddress = c.Address
Do
c.ClearContents
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> FirstAddress
End If
End With

End Sub