Clear cell data that does not contain specific value

[maidenmac666]

I have a code that clears data in a cell not containing the word (Worknotes). However, for 240 rows of data the code ran for an hour. Please assist on how to reduce the time to process as I am looking at using this for a much larger set of data.

Sub ClearCells()


Dim rng As Range
Dim cell As Range
Dim ContainWord As String


Set rng = Range("N2:AJT240")


ContainWord = "(Work notes)"


For Each cell In rng.Cells
If cell.Find(ContainWord) Is Nothing Then cell.Clear
Next cell


End Sub

[mana]

Sub test()
Dim rng As Range
Dim cell As Range
Dim ContainWord As String
Dim u As Range

Set rng = Range("N2:AJT240")
ContainWord = "(Work notes)"

For Each cell In rng.SpecialCells(xlCellTypeConstants)
If InStr(cell.Value, ContainWord) = 0 Then
If u Is Nothing Then
Set u = cell
Else
Set u = Union(u, cell)
End If
End If
Next cell

If Not u Is Nothing Then u.Clear

End Sub

[maidenmac666]

Thanks for the response. However, this script also is taking a lot of time to run and excel stops responding.

[SamT]

Range("N2:AJT240"): That is a very large range = 238 rows time 256 to the 6 power of 256 columns. Or something like that. Whatever, It's huuuuge.

Is there no way to shrink that? Example of only certain columns:

With Rows(2:240)
With Columns("n,z, aj, abn, abz,etc

Query? Do more cells contain that string or do more NOT contain that string?

[snb]

Sub M_snb()
sn= Range("N2:AJT240")

for j=1 to ubound(sn)
for jj=1 to ubound(sn,2)
if instr(sn(j,jj),"Work notes")=0 then sn(j,jj)=""
next
next

Range("N2:AJT240")=sn
End Sub

[SamT]

Betchas snb's code works faster than you can count to 3

[snb]

@Sam

,875 sec on my system.

[maidenmac666]

I get a runtime error '13', Type mismatch. This part of the code is highlighted:

if instr(sn(j,jj),"Work notes")=0 then

[maidenmac666]

There is a lot of data that is not required from N2:AJT240. The data exists due to a text to columns for which we need to get a particular set of data. This data does not sit under a particular column. It is spread randomly.

[snb]

So what is the value of sn(j,jj) in this case ?

remove all errors in the range before running the macro.

[maidenmac666]

I'm sorry I do not follow. very new to VBA

[SamT]

The data exists due to a text to columns
What is the format of the raw data?