Hide Cells on Another Sheet that Equal Value from Drop Down List

[cjvenables]

Hello,

I'm trying to hide all rows on the "Retrieve" sheet that do not equal the value in cell C4 on the "Cover" Sheet. I'd like the rows to hide/unhide as the value in cell C4 changes. I tried to write some code, but it is not hiding the rows on the Retrieve sheet, just row A3 (which has no value in it).

Cell C4 on "Cover" will match Column A on the "Retrieve" sheet. I want only the matched rows to be shown.

I tried to get as far as I could, but nothing seems to work.

Thanks,

Sub Hide()

Dim rng As Range
Dim r As Range

Set rng = Sheets("Retrieve").Range("A4:A" & Range("B" & Rows.Count).End(xlUp).Row)

For Each r In rng
    If r.Value <> Sheets("Cover").Range("C4").Value Then r.EntireRow.Hidden = True
Next r

End Sub

 

[JLGWhiz]

See if this will do what you want.

Sub hideRows()
Dim sh1 As Worksheet, sh2 As Worksheet, lr As Long, rng As Range, vRng As Range
Set sh1 = Sheets("Cover")
Set sh2 = Sheets("Retrieve")
lr = sh2.Cells.Find("*", sh1.Range("A1"), xlValues, xlPart, xlByRows, xlPrevious).Row
sh2.UsedRange.Rows.Hidden = False
sh2.UsedRange.AutoFilter 1, sh1.Range("C4").Value
Set rng = sh2.Range("A:A").SpecialCells(xlCellTypeVisible).EntireRow
sh2.UsedRange.AutoFilter
sh2.UsedRange.Rows.Hidden = True
rng.Rows.Hidden = False
End Sub

 

[cjvenables]

@JLGWhiz This works well. Any way you can make it a worksheet change event so that every time I change the value in C4, the other sheet changes?

Thanks so much! I appreciate the help!

 

[cjvenables]

Also, my sheet has information i need in rows 2 and 3, so the code should start at A4. I tried to put in the following, but it still hides rows 2 and 3.

Set rng = sh2.Range("A4:A" & Range("B" & Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeVisible).EntireRow

 

[JLGWhiz]

This goes in the Sheets("Cover") code module.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$C$4" Then Exit Sub
Dim sh2 As Worksheet, lr As Long, rng As Range, vRng As Range
Set sh2 = Sheets("Retrieve")
sh2.UsedRange.Rows.Hidden = False
lr = sh2.Cells.Find("*", sh2.Range("A1"), xlValues, xlPart, xlByRows, xlPrevious).Row
Set vRng = sh2.Range("A3:A" & lr).EntireRow
vRng.AutoFilter 1, Target.Value
Set rng = sh2.Range("A4:A" & lr).SpecialCells(xlCellTypeVisible).EntireRow
sh2.UsedRange.AutoFilter
sh2.Range("A4:A" & lr).Rows.Hidden = True
rng.Rows.Hidden = False
End Sub