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

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,125
Office Version
  1. 365
Platform
  1. Windows
Is M1 a data validation drop down?
If not what exactly is it?
 

ChrisO'Brien

New Member
Joined
Jan 23, 2013
Messages
13
Hi,

Cell M1 is setup as a list using data validation

The source of the list is
=$A$56:$A$60

A56 = "MA"
A57 = "SA"
A58 = "P3"
A59 = "P4"
A60 = "P5"
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,125
Office Version
  1. 365
Platform
  1. Windows
Ok, how about
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").AutoFilter 3
      Else
         Range("A6").AutoFilter 3, Target.Value
      End If
   End If
End Sub
This needs to go in the relevant sheets code module.
 

ChrisO'Brien

New Member
Joined
Jan 23, 2013
Messages
13

ADVERTISEMENT

Hi, I pasted this in and it worked perfectly, I closed the sheet without saving because in testing a screwed up some of my data.

I opened it up again and used the code as before:

i.e.

Right clicking the sheet.
Clicking view code
Pasting the code into the text area of the "general" area
Doing so changes the "general" text into "Worksheet" text and "Declarations" text to "change" text

however now your VBA code doesn't work at all - what am I doing wrong this time around?

screenshot.jpg
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,125
Office Version
  1. 365
Platform
  1. Windows
You will need to save the file as a macro enabled file, otherwise you will loose the code when you close the workbook.

Do you get any error messages?
 

ChrisO'Brien

New Member
Joined
Jan 23, 2013
Messages
13

ADVERTISEMENT

Thanks for your swift responses and help so far.

I do not get any error messages, when I click M1 and select the various options no rows are hidden or unhidden - which definitely happened the 1st time I used it. I seen your code work perfectly.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,125
Office Version
  1. 365
Platform
  1. Windows
Ok, put this into a standard module & run it, then try changing the value in M1 again.
 

ChrisO'Brien

New Member
Joined
Jan 23, 2013
Messages
13
The code doesn't like it when M1 is a list but when I directly type the Initials "MA, SA etc " it kind of works however it is hiding rows 1 to 6 which I need to show. Any ideas as to how I can always show rows 1 to 6
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,125
Office Version
  1. 365
Platform
  1. Windows
What is the final column of data?
 

Watch MrExcel Video

Forum statistics

Threads
1,114,013
Messages
5,545,494
Members
410,686
Latest member
Fer9us
Top