Hide/Unhide rows based on value on the first cell of the row

[Akukorhonen]

Hey all!

A newbie to VBA here. I've been trying to search for this online already but couldn't find anything to fit my needs.

Here's the case:

I have created a sheet for tracking courses for 5 majors in my university. Based on a cell value B14 (dropdown list for choosing your major), the column A values will change to either to the course code (IEM311 for example) or to 9999 (meaning the row should be hidden). I'd need a code to do this and it should work both ways, hide and unhide, since some rows will be visible only on certain majors, and so on.

Rows the should be hidden(9999)/shown(other) based on Column A value. The range is A29:A180

Thanks for any help!

Cheers,
Aku

 

[wideboydixon]

Public Sub HideUnhide9999()

Dim i As Long
For i = 29 To 180
    Cells(i, 1).EntireRow.Hidden = Cells(i, 1).Value = 9999
Next i

End Sub

WBD

 

[Joe4]

Welcome to the Board!

A simple non-VBA solution would be just to use Filters. You can easily hide/unhide all rows equal to 9999 using Filters.

 

[Akukorhonen]

Welcome to the Board!

A simple non-VBA solution would be just to use Filters. You can easily hide/unhide all rows equal to 9999 using Filters.

Hey!

Thanks for a lighting fast reply.

This sheet is supposed to be used by students of all majors, hence a single one time filtering won't work for this purpose. For example, if I choose a major "Entrepreneurship" in cell B14, then filter out all the 9999's, after choosing a different major, the same filtering still applies and has to be filtered again. Student's won't be doing filtering and it should be automated. Maybe there's a macro for re-filtering column A, when the major is changed?

-AK

 

[Akukorhonen]

Hello!

Thank you for your time to help me out!

Just a quick question. I'm getting an error 13 "Type mismatch" while applying your code. Is there some basics I should know before or how this could be? I'll try again tomorrow after a possible reply, and will get back to you.

-AK

 

[wideboydixon]

When you get the error, Press Ctrl+G and type the following into the Immediate window:

? i
? Cells(i, 1).Value
? TypeName(Cells(i, 1).Value)

Each line will produce an output; paste the result here.

WBD

 

[Akukorhonen]

When you get the error, Press Ctrl+G and type the following into the Immediate window:

? i
? Cells(i, 1).Value
? TypeName(Cells(i, 1).Value)

Each line will produce an output; paste the result here.

WBD

Did what you instructed. When I pasted your code in the immediate window I got a Runtime error '1004': Application-defined or object-defined error.

 

[Akukorhonen]

Your initial code does in fact hide some rows, but when changing between majors, the rows still remains hiddes when they should be shown. If there's anything else you need for troubleshooting, let me know.

 

[wideboydixon]

They need to be pasted one at a time, not all together. Sorry I didn't make that clear.

WBD

 

[Akukorhonen]

They need to be pasted one at a time, not all together. Sorry I didn't make that clear.

WBD

The immediate window won't show any outputs for these lines....