Hide Rows per criteria

[2D Rick]

I need to look thru a range " Worksheets(1).Range("R4:AI7000") " row
by row and hide the row if "ICG" is not in any of the 18 cells in that
row. Similar to what AutoFilter does only in VBA.

"ICG" can show up once or twice per row and in any position along the
18 cells.

If "ICG" is found, do nothing and check next row.
If found hide the row.

Once printed I would like to un-hide all rows.

Thanks, Rick

 

[Bob Phillips]

For Each cell In range("R4:R7000)
If Application.Countif("ICG",cell.Resize(,18)) > 0 Then
cell.EntireRow.Hidden = True
End If
Next cell

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)

 

[Dave Peterson]

I think you meant this:

If Application.Countif(cell.Resize(,18), "ICG") > 0 Then

 

[2D Rick]

Not being experienced with Excel VBA some of your code escapes me.
This code appears to hide any row that contains "ICG" where I want to
hide everything but.
Will it work with my range: range("R4:AI7000)
This approach seems like it would take quite a while to loop thru all
my cells, evaluate and hide?
Tell me I'm wrong.

Thanks for the quick replys,
Rick

 

[Roger Govier]

Hi Rick

Change the line of Bob's code (which Dave amended) to
If Application.Countif(cell.Resize(,18), "ICG") = 0 Then
and that will hide the rows where ICG does not appear.

Also change the initial range from R4:R7000 to R4:R17000

The code does not have to iterate through all cells in the range.
The countif with resize 18 is looking quickly along the whole row, but
it does of course have to iterate through all lines.
As to how long it takes - just try it.

 

[Bob Phillips]

Also, the time can be mitigated somewhat

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For Each cell In range("R4:R17000")
If Application.Countif(cell.Resize(,18),"ICG") = 0 Then
cell.EntireRow.Hidden = True
End If
Next cell
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic


If this is still slow, post back and we will give you a different method.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[2D Rick]

Thanks Roger and Bob,
I was bound and determined to use MY RANGE "Range("R4:AI7000")" and
couldn't understand why you kept changing it.
Doh........, once I left your code alone, realizing what the Resize was
doing, it went right to work doing exactly what I needed.
The time required to return its results are more than acceptable.
This was way over my head, Thanks for the speedy solution.
Rick

 

[Bob Phillips]

For interest Rick, what was it previously, and what is it now? I am sure we
can do it quicker for you.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)