Hiding rows, whose value are more than targeted cell,(Except Macros)

[ram]

Targeted Cell Value 5

Array

Sr.

1

2

3

4

5

6

7

8

9

10

want to hide the rows matching the targeted cell as well as value in the array which are more than targeted cell value

 

[Luke M]

Is there a specific reason you don't want to use a macro? Using just formula, the best you would be able to do would be to a) format the offsensive rows to be whtie font, or b) transfer all the good rows via formulas.


If you change your mind and don't mind an event macro, you can use this. Simply right click on sheet tab, view code, and paste this in. Modify where needed:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim MyCell As String

MyCell = "A2" 'Cell of interest

If Intersect(Range(MyCell), Target) Is Nothing Then Exit Sub

Application.EnableEvents = False

For i = 4 To 10 'Define range of rows to hide/unhide

Cells(i, 1).EntireRow.Hidden = (Cells(i, 1).Value >= Range(MyCell).Value)

Next

Application.EnableEvents = True

End Sub

 

[ram]

Luke specifically i don't know VBA, still looking 4r kind support

 

[Luke M]

The macro I wrote should be ready to go, all you would need to do it copy and paste. You'll need to modify this line:

MyCell = "A2" 'Cell of interest

by changing the reference to whatever cell contains your target value. The other line you'd need to change is this one:

For i = 4 To 10 'Define range of rows to hide/unhide

Which states which range of rows to look at. Currently, this looks at rows 4 to 10. Once you've got it set, you can close the VB editor, and you're good to go.

I admist that VBA can be a little intimidating at first, but I think that once you get used to it, you'll soon realize how invaluable a tool it is!