PDA

View Full Version : Clear cell data that does not contain specific value



maidenmac666
09-25-2019, 06:00 AM
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
09-25-2019, 06:35 AM
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
09-25-2019, 09:31 AM
Thanks for the response. However, this script also is taking a lot of time to run and excel stops responding.

SamT
09-25-2019, 10:56 AM
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
09-25-2019, 02:40 PM
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
09-25-2019, 03:10 PM
Betchas snb's code works faster than you can count to 3

snb
09-26-2019, 01:10 AM
@Sam

,875 sec on my system.

maidenmac666
09-26-2019, 03:03 AM
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
09-26-2019, 03:07 AM
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
09-26-2019, 03:14 AM
So what is the value of sn(j,jj) in this case ?

remove all errors in the range before running the macro.

maidenmac666
09-26-2019, 03:26 AM
I'm sorry I do not follow. very new to VBA

SamT
09-26-2019, 06:51 AM
The data exists due to a text to columns
What is the format of the raw data?