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

cjvenables

Board Regular
Joined
Aug 2, 2011
Messages
65
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,

Code:
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
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
See if this will do what you want.
Code:
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

Board Regular
Joined
Aug 2, 2011
Messages
65
@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

Board Regular
Joined
Aug 2, 2011
Messages
65
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

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
This goes in the Sheets("Cover") code module.
Code:
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
 

Forum statistics

Threads
1,136,354
Messages
5,675,296
Members
419,559
Latest member
BraytonM

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top