Hiding cells in one sheet based on a manually entered value in a separate sheet

[KBW90]

Hi,

I am trying to hide rows in sheet 4 of my workbook that don't contain the value of cell B4 in sheet 1.

In cell B4 of sheet 1 in my workbook I will be manually entering a portfolio number. Sheet 4 contains a full list of all portfolios (558 rows). I am trying, thus far very unsuccessfully, to write a code which will hide all of the rows between row 6 and row 558 in sheet 4 that do not contain the portfolio number manually entered into cell B4 of sheet A1. I would love it to be automatic so that when I enter a new portfolio number I can run the macro and only the rows which contain that number are displayed.

In sheet 4 which contains the full list of portfolios the portfolio number is in column A (from row 6 to 558), the other columns contain information which correspond to this portfolio and as such I don't want the other columns to be hidden just the rows.

If this doesn't make sense/needs elaboration let me know.

Any help would be much appreciated!!

Thanks.​

 

[BlueHornet]

Welcome to the board.

Here is some code that will do what you want, but keep in mind that this is not "secure" (if you're trying to develop something that will prevent someone from viewing things that they don't have permission to view, for example), but simply "handy" to accomplish what you've asked for.

Assuming the Portfolio Number in Sheet 1 will be entered in cell A2 (just modify the code below to suit, otherwise, including the Sheet Name), what will work for you is a Worksheet_Activate() (and later a Worksheet_Deactivate() routine as well, perhaps) as follows:

Right-click on the tab for Sheet 4 and "View Code".
In the VBA screen that opens up, copy this code:

Private Sub Worksheet_Activate()
Dim Counter As Integer
Application.ScreenUpdating = False


For Counter = 4 To 588
    If Cells(Counter, 1) <> Sheets(1).Range("A2").Value Then
        Rows(Counter & ":" & Counter).EntireRow.Hidden = True
    End If


Next Counter
Application.ScreenUpdating = True
End Sub


Private Sub Worksheet_Deactivate()


Rows("4:588").EntireRow.Hidden = False


End Sub

Now when you activate Sheet 4, only portfolios whose name in A4:A588 matches the value of cell A2 in Sheet 1 will be visible. (But it's still a very simple matter to unhide the rows, which is why there is zero security attached here.)

Deactivating the sheet unhides the rows again.

 

[KBW90]

Thank you so much that worked! You don't know how long I've been trying to do that. Cheers!

 

[BlueHornet]

You're welcome. I'm glad it worked, and thanks for the feedback.