Hide rows containing the word Culled

[Lymm]

Hi, I tried to hide rows containing the word Culled with this code . It seemed to go through the range but no rows were hidden, what have I done wrong.

Sub HideRows()
Dim cell As Range
For Each cell In Range("A2:Q150")
    cell.EntireRow.Hidden = cell.Value = "Culled"
Next cell
End Sub

 

[lohithsriram]

Hi Lymm,

Change your line of code to this. But, there are better ways to do this job.

Sub HideRows()
Dim cell As Range
For Each cell In Range("A2:Q150")
If cell.Value = "Culled" Then cell.EntireRow.Hidden = True
'cell.EntireRow.Hidden = cell.Value = "Culled"
Next cell
End Sub

 

[NARAYANK991]

Hi Lymm ,

In case you are still interested to know why your code did not work , the problem is the statement :

cell.EntireRow.Hidden = cell.Value = "Culled"

What you want to do is the following :

If the word Culled appears anywhere in a row ( between the columns A and Q , and the rows 2 and 150 ) , then hide that row.

The problem with the statement the way it has been phrased , is that it does the following :

If the word Culled appears anywhere in a row ( between the columns A and Q , and the rows 2 and 150 ) , then hide that row , else unhide that row.

The FOR EACH .... NEXT statement goes through the range A2:Q150 row by row i.e. it examines the cells in the following order :

A2 , B2 , C2 ,...., P2 , Q2 ,
A3 , B3 , C3 ,...., P3 , Q3 ,
.
.
.
A150 , B150 , C150 ,...., P150 , Q150.

Thus if the word Culled appears in cell A37 , then the statement hides row 37 ; when it goes to the next cell which is B37 , and does not find the word Culled in it , it unhides the row !

The way Lohith Sriram has phrased the statement is the correct way , since if the word does not appear in a cell , the statement does nothing.

Narayan

 

[Lymm]

HI, Lohith, thank you. I tried the following code and it seems to work

Option Explicit
Dim cel As Range, rng As Range
 
Sub HideRows2()
     
    Application.ScreenUpdating = False
     
    Set rng = Range("A2", Range("Q65536").End(xlUp))
     
    For Each cel In rng
         
        If cel.Value = "Culled" Then
             
            cel.EntireRow.Hidden = True
             
        End If
         
    Next cel
     
    Application.ScreenUpdating = True
     
End Sub

What would you suggest as a better way to do this? Thanks

 

[Lymm]

Thank you Narayank99 for the explaination, it is good to know why things dont work and helps to understand what does work, I appreciate your input.

 

[Smallman]

Iterations in Loop of A2:Q150 EQUALS 2533. Lets let a bit of simplicity march into the equation....

This in R2 copied down;

=COUNTIF($A2:$Q2,"Culled")

This in VB;

Sub testo()
    Range("R1:R150").AutoFilter 1, "=0", , , 0
End Sub

Iterations above - Zero!!! Rows hidden, job done. Workbook attached to prove workings.

Take care

Smallman

 

[Lymm]

, Cool, there are so many ways to do things, thanks. Will countif accept more than one variable? Some of the rows I ended up needing to hide contained "died" or "sold". I added OR to my macro and it worked ok. I will experiment and see.

Ok, i used sumproduct (Excel 2003)

=SUMPRODUCT((A2:Q2="Culled")+(A2:Q2="Sold")+(A2:Q2="Died"))