Automatically Hide Rows Based On Cell Ref

[ChrisO'Brien]

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:

Date	Battery Set	Remote Pilot	Crew Members	Location	Task	OSC	CA	Take off time	Land time	Flight Time
		MA
14-Aug-20	A	SA						16:53	17:05	12:31
	E	SA						17:08	17:21	13:48
	D	P1						17:58	18:13	15:33
12-Aug-20	A	P2					CA	14:45	14:59	14:21
	B	MA					CA	15:06	15:19	13:32
	C	MA					CA	15:21	15:29	8:25
31-Jul-11	A	MA					CA	08:54	09:07	13:42
	B	MA					CA	09:22	09:34	12:59

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

 

[Fluff]

Is M1 a data validation drop down?
If not what exactly is it?

 

[ChrisO'Brien]

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]

Ok, how about

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]

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?

 

[Fluff]

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]

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]

Ok, put this into a standard module & run it, then try changing the value in M1 again.

 

[ChrisO'Brien]

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]

What is the final column of data?