Automatically Hide Rows Based On Cell Ref

ChrisO'Brien

New Member
Joined
Jan 23, 2013
Messages
13
Hi,

I would like a page within my macro enabled workbook to automatically hide and unhide rows based on the cell reference within column C of each row.

Page Name "Pilot Log"

Possible text references within column C would be

"MA"
"SA"
"P3"
"P4"
"P5"

The rows I would like to hide / unhide automatically start at row 7 and continue down the sheet to row 43.

Row 6 is the row that displays "Date / Battery Set/ Remote Pilot/ Location / Task etc" and needs to be visible. Infact Rows 1-6 always need to be visible.

Cell Ref "M1" in my sheet is my control box and is a data list. I propose to click this and select say "MA" from a list. Doing so would hide all rows without "MA" in their column C.
Deleting it would show everything and selecting something else from the list would show only the rows with that specific reference.

Example:

DateBattery SetRemote PilotCrew MembersLocationTaskOSCCATake off timeLand timeFlight Time
MA
14-Aug-20ASA16:5317:0512:31
ESA17:0817:2113:48
DP117:5818:1315:33
12-Aug-20AP2CA14:4514:5914:21
BMACA15:0615:1913:32
CMACA15:2115:298:25
31-Jul-11AMACA08:5409:0713:42
BMACA09:2209:3412:59

Thanks In Advance for any help, I hope all is clear.
 

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,130
Office Version
  1. 365
Platform
  1. Windows
OK, try
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Address(0, 0) = "M1" Then
      If Target.Value = "" Then
         Range("A6:K6").AutoFilter 3
      Else
         Range("A6:K6").AutoFilter 3, Target.Value
      End If
   End If
End Sub
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,130
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,019
Messages
5,545,531
Members
410,689
Latest member
ConfuzzledThomas
Top