why is this code hiding all rows in range?

[orsm6]

hi - when i step through this code i found it hides all the rows in column a4:a50 that have nothing in them (they contain formulas, but cell is blank) but when i call the code as part of another macro it hides every row in a4:a50 regardless if it has values.

every row in that range has a formula, but if a4:50 is blank then the rest of the columns are blank too.

Sub hiderows()
Worksheets("Layout").Activate

Dim c As Range
Rows("4:50").EntireRow.Hidden = False
        'rehide the blanks
            For Each c In Range("a4:a50")
                If c.Value = "" Then
                    c.EntireRow.Hidden = True
                Else
                    'do nothing, can resinstate the command below, but not required
                    c.EntireRow.Hidden = False
            End If
            Next
End Sub

 

[orsm6]

Maybe this way

Sub hiderows()
Worksheets("Layout").Activate
Dim c As Long
Rows("4:50").EntireRow.Hidden = False
For c = 4 To 50
    If Len(Range("A" & c)) = 0 Then Rows(c).EntireRow.Hidden = True
Next c
End Sub

Hi Michael - no unfortunately still hides all the rows.

 

[Michael M]

In that case there not blank...what formula is in thos cells ?

 

[orsm6]

In that case there not blank...what formula is in thos cells ?

thats correct. my first post said that column A5:A50 has formulas. the formula is: =IFERROR(IF(D5=0,"",IF(ISNUMBER(SEARCH("WHEY LOSS",C5)),"Z002",INDEX(CK13a!$H$18:$H$100,MATCH(Layout!B5,CK13a!$L$18:$L$100,0)))),"")

 

[Michael M]

Yep, I understand that...if you put in an adjacent column =LEN(A4) and drag down what result do you get ?
Also, can you upload your worksheet / s to a hosting site, DropBox for instance, and someone will take a look at it.

 

[orsm6]

Yep, I understand that...if you put in an adjacent column =LEN(A4) and drag down what result do you get ?
Also, can you upload your worksheet / s to a hosting site, DropBox for instance, and someone will take a look at it.

i have a helper column (Z) if that helps? it contains 1 if the row is empty, 2 if it is not (a formula is used to get this)
no sorry i can't upload it as it contains sensitive ingredients and recipes relating to my workplace.

 

[orsm6]

i have a helper column (Z) if that helps? it contains 1 if the row is empty, 2 if it is not (a formula is used to get this)
no sorry i can't upload it as it contains sensitive ingredients and recipes relating to my workplace.

sorry to answer you question.... it returns the number 4 if the cell is 'blank' but 0 if it has a visible value

 

[Michael M]

So, if you are using the formula I provided and you get 4
it returns the number 4 if the cell is 'blank'
It is not a blank cell and therefore won't be hidden

 

[orsm6]

here is column A

IBR - New2.xlsm
	A
4	Material Type
5
6	Z004
7	Z004
8	Z004
9	Z004
10	Z004
11	Z004
14	Z003
15	Z003
16	Z003
Layout

Cell Formulas
Range	Formula
A14:A16,A5:A11	A5	=IFERROR(IF(D5=0,"",IF(ISNUMBER(SEARCH("WHEY LOSS",C5)),"Z002",INDEX(CK13a!$H$18:$H$100,MATCH(Layout!B5,CK13a!$L$18:$L$100,0)))),"")

here are the results in column Z

IBR - New2.xlsm
	Z
4	helper don’t delete
5	0
6	4
7	4
8	4
9	4
10	4
11	4
14	4
15	4
16	4
Layout

Cell Formulas
Range	Formula
Z14:Z16,Z5:Z11	Z5	=LEN(A5)

 

[orsm6]

So, if you are using the formula I provided and you get 4
it returns the number 4 if the cell is 'blank'
It is not a blank cell and therefore won't be hidden

correct not "blank" as i mentioned earlier.... but still need the rows hiddean that "appear" to be blank. that was why i tried a helper column to hide based on number, but it didn't work for me

 

[Michael M]

So if Z is the helper, Does this work for you

Sub hiderows()
Worksheets("Layout").Activate
Dim c As Long
Rows("5:50").EntireRow.Hidden = False
For c = 5 To 50
    If Range("Z" & c) = 0 Then Rows(c).EntireRow.Hidden = True
Next c
End Sub