Auto hide/unhide random rows using Macros

[prmk1234]

Hi, I am very new to macros and try to use them by visiting similar queries and trying out the suggested codes. But this one problem I am not able to solve. I am not sure whether this exact thing has been covered or not hence I am posting this.

1. So I have a set of values in a drop-down(from data-validation) viz. Select,A,B,C,D,E,F in cell "C1"2. In Column A, specifically from A5 and down, I have these drop-down values appearing as a part of a table in random order. This column would be manual hidden from users' view(just for aesthetics).
3. If I select a value in C1, only the rows having the same value in column A should be unhidden and rest should hide (even the rows which are blank) and the whole thing should be dynamic.
4. On selecting the value "Select", all rows should unhide.
5. The rows are random, i.e. the order in which the values of drop-down appear in rows is not in order as the Users may add additional values at the bottom of list, irrespective of the order. Hence I can't use case, I suppose.

I used the following code. It works partially but the problem is every time I select a value from drop-down, though it does the operation but some or the other run-time error box pops up with option to either "End" or "Debug". And it closes the sheet. When I reopen, sometimes the previous attempt is successful only that in case of any blank values in column A, those rows are not hidden.

The code -

Option Explicit
Option Compare Text
Dim cel As Range, rng As Range
Private Sub Worksheet_Change(ByVal Target As Range)
    Application.ScreenUpdating = False
    Rows.Hidden = False
    Target = Range("C1").Value
    Set rng = Range("A5", Range("A5000").End(xlUp))
    If Target = "Select" Then       Rows.Hidden = False
    Else
       For Each cel In rng
          If Not cel.Value = Target Then
          cel.EntireRow.Hidden = True
          End If
       Next cel
    End If
    Application.ScreenUpdating = True
     
End Sub

Kindly let me know where is the problem. Also, am I suppose to write it in Sheet1 or Module1 window(like I said I am very new to the whole thing)


Thanks

 

[Andrew Poulsom]

Try removing this line:

Target = Range("C1").Value

The code goes in the module for the worksheet.

 

[prmk1234]

Try removing this line:

Target = Range("C1").Value

The code goes in the module for the worksheet.

Thank you, it works. I mean it does work without any errors.
But I am still stuck at how to hide any rows which might have blank cells for the same column (i.e. A5 to A5000).

What should be the condition to hide any rows with blank cells when checked.