Hide rows based on cell data

[Munchkin]

My spreadsheet shows 6 different department's file records. Colum J
specifies Dept A, Dept B, Dept C, etc. I'd like to create a macro button
for each Dept that would show just thier records and hide the others, but I
don't know how. Can anyone help?

 

[Ron]

My spreadsheet shows 6 different department's file records.  Colum J
specifies Dept A, Dept B, Dept C, etc.   I'd like to create a macro button
for each Dept that would show just thier records and hide the others, butI
don't know how.  Can anyone help?

Hi Munchkin, why not use Excel's Auto Filter?

 

[Patrick Molloy]

have you tried the Autofilter? that's exactly what it can do for you

 

[Munchkin]

I use autofilter all the time, but the people using this spreadsheet are not
familiar with Excel & would not be receptive to learning how to use
Autofilter. My worksheet has various macros assigned to buttons to make this
super easy. I tried writing a macro that used autofilter, but when I tried
to run it I got an error message, so I thought you could not use autofilter
in a macro.

I'm completely self taught at this stuff....so I'm an amature at this stuff.

Can you use autofilter in a macro?

 

[Gord Dibben]

You can run autofilter from a macro.

Instead of "writing" the macro, try using the macro recorder.

When you have the code for that, you can refine it to event code which runs
when users select a Dept from a Data Validation dropdown list.


Gord Dibben MS Excel MVP

 

[Ron]

Hi Munchkin,
This should work for you. Here's the code for button A. Just
duplicate it to address b, c, and d. It is case sensitive. Have fun,
Ron

Sub HiddenRows()
Dim RowNdx As Long
Dim LastRow As Long

Application.ScreenUpdating = False
ActiveSheet.Rows.Hidden = False

LastRow = ActiveSheet.Cells(Rows.Count, "J").End(xlUp).Row

For RowNdx = LastRow To 1 Step -1
If Cells(RowNdx, "J") = "dept b" Then
Rows(RowNdx).Hidden = True
End If
If Cells(RowNdx, "J") = "dept c" Then
Rows(RowNdx).Hidden = True
End If
If Cells(RowNdx, "J") = "dept d" Then
Rows(RowNdx).Hidden = True
End If

Next RowNdx
Range("A1").Select
End Sub