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

 

[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

Hey again,

Here are the outputs for these lines:

? i
50


? Cells(i, 1).Value
Error 2042


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

How to proceed now?

 

[wideboydixon]

So cell A50 has an error - perhaps #N/A?. Maybe just ignore it like this:

Public Sub HideUnhide9999()


On Error Resume Next


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


End Sub

WBD

 

[Akukorhonen]

So cell A50 has an error - perhaps #N/A?. Maybe just ignore it like this:

Public Sub HideUnhide9999()


On Error Resume Next


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


End Sub

WBD

Awesome! This works almost as I need to. Now it hides all the cells that needs to be hidden, but it also need to unhide and hide again everything when I change the major. Is there a loop I could use?