Conditional Row visible & hide for streaming worksheet

[sandip_dave]

I have a worksheet with streaming data of stock value, high, low, stock name in Column A3:A103, B3:B103, C3:C103, E3:E103 respectively and other columns with formulas and that updates every few seconds.


I would like to see only rows where below conditions is fulfilled and hide all other rows to filter out stocks.


Condition 1: =AND(A3=B3,A3>=L3,A3<=K3,A3<>0)
Condition 2 =AND(A3=C3,A3>=S3,A3<=R3,A3<>0)


Above condition is for example of Raw No.3 and which I am using currently for conditional formatting.


Kindly help to provide VBA codes for this.

 

[S_Wish]

For i = 3 To 103
If Not (Range("A" & i) = Range("B" & i) And Range("A" & i) >= Range("L" & i) And Range("A" & i) <= Range("K" & i) And Range("A" & i) <> 0) Then
Rows(i).Hidden = True
End If
If Not (Range("A" & i) = Range("C" & i) And Range("A" & i) >= Range("S" & i) And Range("A" & i) <= Range("R" & i) And Range("A" & i) <> 0) Then
Rows(i).Hidden = True
End If
Next i

try this

 

[sandip_dave]

For i = 3 To 103
If Not (Range("A" & i) = Range("B" & i) And Range("A" & i) >= Range("L" & i) And Range("A" & i) <= Range("K" & i) And Range("A" & i) <> 0) Then
Rows(i).Hidden = True
End If
If Not (Range("A" & i) = Range("C" & i) And Range("A" & i) >= Range("S" & i) And Range("A" & i) <= Range("R" & i) And Range("A" & i) <> 0) Then
Rows(i).Hidden = True
End If
Next i

try this

Thanks S_Wish, however you have not considered A3<=K3 in condition - 1 and A3<=R3 in condition - 2.

In short I need to display only rows where either of below 2 conditions are fulfilled;

Condition 1: =AND(A3=B3,A3>=L3,A3<=K3,A3<>0)
Condition 2 =AND(A3=C3,A3>=S3,A3<=R3,A3<>0)

Please update the code as per above;
Thanks

 

[S_Wish]

so you need a condition like
=AND(A3=B3,A3=C3,A3>=L3,A3>=S3,A3<=R3,A3<=K3,A3<>0)
I did it, see bold

​For i = 3 To 103
If Not (Range("A" & i) >= Range("S" & i) And Range("A" & i) <= Range("R" & i) And Range("A" & i) = Range("C" & i) And Range("A" & i) = Range("B" & i) And Range("A" & i) >= Range("L" & i) And Range("A" & i) <= Range("K" & i) And Range("A" & i) <> 0) Then
Rows(i).Hidden = True
End If
Next i

 

[sandip_dave]

Thanks S_Wish, however you have not considered A3<=K3 in condition - 1 and A3<=R3 in condition - 2.

In short I need to display only rows where either of below 2 conditions are fulfilled;

Condition 1: =AND(A3=B3,A3>=L3,A3<=K3,A3<>0)
Condition 2 =AND(A3=C3,A3>=S3,A3<=R3,A3<>0)

Please update the code as per above;
Thanks

Sorry for my mistake

you considered all.

Just need to confirm that will be it work in live streaming work sheet as data is not filled manually and it auto updates live..

 

[sandip_dave]

I just pasted the code in worksheet but not working.

whether i should add anything above/below provided code?

 

[S_Wish]

Sub RowsHide()
For i = 3 To 103
If Not (Range("A" & i) >= Range("S" & i) And Range("A" & i) <= Range("R" & i) And Range("A" & i) = Range("C" & i) And Range("A" & i) = Range("B" & i) And Range("A" & i) >= Range("L" & i) And Range("A" & i) <= Range("K" & i) And Range("A" & i) <> 0) Then
Rows(i).Hidden = True
End If
Next i
End Sub

 

[S_Wish]

I understood what`s problem, you do not want VBA macros, just function like =...
have no idea how to do it there

 

[sandip_dave]

I understood what`s problem, you do not want VBA macros, just function like =...
have no idea how to do it there

Sorry S_wish but I am very new to VBA codes.


The code is not working perfectly and hiding all the rows of data at initial run.

When I removed one of the condition as below; it worked for condition-2 but when streaming data changed and making condition-2 in other rows it does not becomes visible.



Sub RowsHide()
For i = 3 To 103


If Not (Range("A" & i) = Range("C" & i) And Range("A" & i) >= Range("S" & i) And Range("A" & i) <= Range("R" & i) And Range("A" & i) <> 0) Then
Rows(i).Hidden = True


End If
Next i
End Sub



In short,


1) code is not working with both conditions
2) It only runs once and hides all rows. It should run continuously make rows visible when either conditions be fulfilled.

Please help