Search for cells that DO NOT contain specific text
 

Hi,

I am trying to write a macro that checks each row for a specific text in a
column.
If it does not contain the text, I want to hide the row.
....BUT only if checkbox1 is checked (true).

I thought it would be something like the below, but I don't have it right....

I am doing this within a For...next loop.

If Cells(RowNumber, SearchColumn) < "*SPECIFIC TEXT*" And CheckBox1
= True Then
'Select row
Rows(RowNumber).Select
'Hide row
Selection.EntireRow.Hidden = True

Any help appreciated.
Thanks.

Search for cells that DO NOT contain specific text
 

Try something more like this... (untested)

dim rngToSearch as range
dim rngToHide As range
dim rng as range

if checkbox1 = tue then
set rngtosearch = range(Range("A1"), cells(rows.count, "A").end(xlup))

for each rng in rngtosearch
if instr("SpecificText", rng.value) < 0 then 'you could use like
if rngToHide is nothing then
set rngtohide = rng
else
set rngtoHide = union(rng, rngtohide)
end if
next rng
end if

if not rngtohide is nothing then rngtohide.entirerow.hidden = true

--
HTH...

Jim Thomlinson


"HowOnEarthDoI" wrote:

Hi,

I am trying to write a macro that checks each row for a specific text in a
column.
If it does not contain the text, I want to hide the row.
...BUT only if checkbox1 is checked (true).

I thought it would be something like the below, but I don't have it right....

I am doing this within a For...next loop.

If Cells(RowNumber, SearchColumn) < "*SPECIFIC TEXT*" And CheckBox1
= True Then
'Select row
Rows(RowNumber).Select
'Hide row
Selection.EntireRow.Hidden = True

Any help appreciated.
Thanks.

Search for cells that DO NOT contain specific text
 

Hi,

Maybe this

searchcolumn = "A"
For rownumber = 1 To 10
If InStr(Cells(rownumber, searchcolumn), "SPECIFIC TEXT") < 1 And
CheckBox1.Value = True Then
Rows(rownumber).EntireRow.Hidden = True
End If
Next

Mikr

"HowOnEarthDoI" wrote:

Hi,

I am trying to write a macro that checks each row for a specific text in a
column.
If it does not contain the text, I want to hide the row.
...BUT only if checkbox1 is checked (true).

I thought it would be something like the below, but I don't have it right....

I am doing this within a For...next loop.

If Cells(RowNumber, SearchColumn) < "*SPECIFIC TEXT*" And CheckBox1
= True Then
'Select row
Rows(RowNumber).Select
'Hide row
Selection.EntireRow.Hidden = True

Any help appreciated.
Thanks.

Search for cells that DO NOT contain specific text
 

Works BEAUTIFULLY!

Thanks, Mike.

Thanks, Jim, also - really appreciate the quick responses.


"Mike H" wrote:

Hi,

Maybe this

searchcolumn = "A"
For rownumber = 1 To 10
If InStr(Cells(rownumber, searchcolumn), "SPECIFIC TEXT") < 1 And
CheckBox1.Value = True Then
Rows(rownumber).EntireRow.Hidden = True
End If
Next

Mikr

"HowOnEarthDoI" wrote:

Hi,

I am trying to write a macro that checks each row for a specific text in a
column.
If it does not contain the text, I want to hide the row.
...BUT only if checkbox1 is checked (true).

I thought it would be something like the below, but I don't have it right....

I am doing this within a For...next loop.

If Cells(RowNumber, SearchColumn) < "*SPECIFIC TEXT*" And CheckBox1
= True Then
'Select row
Rows(RowNumber).Select
'Hide row
Selection.EntireRow.Hidden = True

Any help appreciated.
Thanks.

Search for cells that DO NOT contain specific text
 

Glad I could help

"HowOnEarthDoI" wrote:

Works BEAUTIFULLY!

Thanks, Mike.

Thanks, Jim, also - really appreciate the quick responses.


"Mike H" wrote:

Hi,

Maybe this

searchcolumn = "A"
For rownumber = 1 To 10
If InStr(Cells(rownumber, searchcolumn), "SPECIFIC TEXT") < 1 And
CheckBox1.Value = True Then
Rows(rownumber).EntireRow.Hidden = True
End If
Next

Mikr

"HowOnEarthDoI" wrote:

Hi,

I am trying to write a macro that checks each row for a specific text in a
column.
If it does not contain the text, I want to hide the row.
...BUT only if checkbox1 is checked (true).

I thought it would be something like the below, but I don't have it right....

I am doing this within a For...next loop.

If Cells(RowNumber, SearchColumn) < "*SPECIFIC TEXT*" And CheckBox1
= True Then
'Select row
Rows(RowNumber).Select
'Hide row
Selection.EntireRow.Hidden = True

Any help appreciated.
Thanks.