PDA

View Full Version : Hide row if ?



softman
10-14-2010, 05:28 AM
Hi, I have the following code below and it work in one row. I would like to knoe if it 's possibe were I have 2 rows with the following senario:

Colom: G And H
If colom G and H is 0 it stay
If colom G or H is 1 or 0 it stay
If colom Gor H is N/A or 0 it stay

But if colom G or H is both 1 it gets hide
If colom G or H is 1 or N/A it gets hide




Sub HURows()
BeginRow = 6
EndRow = 25
ChkCol = 7
For RowCnt = BeginRow To EndRow
If Cells(RowCnt, ChkCol).Value <= 1 Then
Cells(RowCnt, ChkCol).EntireRow.Hidden = True
Else
Cells(RowCnt, ChkCol).EntireRow.Hidden = False
End If
Next RowCnt
End Sub



Simon said: If all you want to see is zero's left then this will do:



VBA:
Dim i As Long For i = Range("G" & Rows.Count).End(xlUp).Row To 1 Step -1 If Range("G" & i) = 1 Or Range("H" & i) = "N/A" Then Rows(i).Hidden = True End If Next i


I am asking:
Hi Simom

I picked up something: when running the macro it works fine but this is what I get:

If colom G has a N/A and colom H a 1 it does not hide.
but it works the other way..

If colom G has a 1 and colom H a N/A it hides.

Same is working with 0, if N/A and 0 it work fine and does not hide
but if it is 0 and N/A it gets hiden, and should not.

Any idea?

From unsolved link:
http://www.vbaexpress.com/forum/showthread.php?t=34481

Bob Phillips
10-14-2010, 11:04 AM
As Simon told you before, this statement

If colom Gor H is N/A or 0 it stay

and this one

If colom G or H is 1 or N/A it gets hide

are in conflict, you cannoty have both, because it is saying if G or H is N/A hide it, but don't hide it.

softman
10-14-2010, 11:29 PM
Sorry for the confusion. This is execly what must happen:

All these must be hidden If it's in G or H:
N/A and 1 or
1 and N/A
or all 1's

This must NOT be hidden:
If there are any zeros in G or H for example,
N/A and 0
0 and N/A
1 and 0
0 and 1
or only 0's

Hope this hepls, sorry for the misunderstanding

Bob Phillips
10-15-2010, 09:32 AM
What about all N/As?

softman
10-18-2010, 12:38 AM
Yes, all N/A must also be hidden

softman
10-19-2010, 02:40 AM
I have found a 90% solution in the KB but need help to hide were G and H is = "N/A"

Sub HideRow()
Dim HiddenRow&, RowRange As Range, RowRangeValue&
'*****************************
'< Set the 1st & last rows to be hidden >
Const FirstRow As Long = 6
Const LastRow As Long = 24
'< Set your columns that contain data >
Const FirstCol As String = "G"
Const LastCol As String = "H"
'*****************************

ActiveWindow.DisplayZeros = True
Application.ScreenUpdating = False
For HiddenRow = FirstRow To LastRow
'(we're using columns B to G here)
Set RowRange = Range(FirstCol & HiddenRow & _
":" & LastCol & HiddenRow)
'sums the entries in cells in the RowRange
RowRangeValue = Application.Sum(RowRange.Value)
If RowRangeValue <= 0 Then
'there's something in this row - don't hide
Rows(HiddenRow).EntireRow.Hidden = False
Else
'there's nothing in this row yet - hide it
Rows(HiddenRow).EntireRow.Hidden = True
End If
Next HiddenRow
Application.ScreenUpdating = True
End Sub


Please see attached