Automating My Excel Project

[ArmySanta]

Hi,

I am automating a course schedule. I want to test the above cell. If its value is "n" then to hide the cells below. I am extremely new to VBA and macros. Could someone please tell me what I am doing wrong?


Sub HideRows_Based_On_Values()
If Range("C2").Value = "n" Then
For Each cell In Range("c3:c20")
If cell.Value = "n" Then cell.EntireRow.Hidden = True
End If
Next cell
End Sub

[Aussiebear]

Try the following


Sub HideRows_Based_On_Values()
If Range("C2").Value = "n" then
For each cell in Range("C3:C20")
.entireRow.Hidden = True
Next Cell
End If
End Sub

[ArmySanta]

Hi Aussiebear,

I wrote out the code as you did, and it is providing a compile error: invalid or unqualified reference. After I press ok, it highlights .EntireRow

Can you help?

Cheers,
Army Santa

[Paul_Hossler]

The 'dot' is looking for a reference to something that identifies the .EntireRow

Try




cell.entireRow.Hidden = True


where 'cell' identifies the .EntireRow



In your original, I think you just had the 'next cell' and the 'End If' out of order

The 'If cell.Value = "n"' line does not require a 'End If' since it's a single line statement



Sub HideRows_Based_On_Values()
If Range("C2").Value = "n" Then
For Each cell In Range("c3:c20")
If cell.Value = "n" Then cell.EntireRow.Hidden = True
Next cell' <<<<<
End If ' <<<<<<
End Sub



It's a little easier to see when you use a more standard indent approach

[ArmySanta]

Good day,

Thank you for the advice and it worked well. I have to do this for about 60 courses on the same excel sheet and up to 20 iterations of the same course. The idea is that it will automatically reveal an iteration as needed and not display all 20. For obvious reasons, I do not want to execute 1200 if statements every time a cell is changed. Handling 80 if statements is a lot easier regarding the processing speed.

If there a way I could simplify the code below? The code displayed is 1 course. My assumption is ElseIf statements, but it is not working.


Sub HideRows_Based_On_Values()

If Range("C2").Value = "n" Then
For Each cell In Range("c3:c21")
If cell.Value = "n" Then cell.EntireRow.Hidden = True
Next cell
End If
If Range("C3").Value = "n" Then
For Each cell In Range("c4:c21")
If cell.Value = "n" Then cell.EntireRow.Hidden = True
Next cell
End If
If Range("C4").Value = "n" Then
For Each cell In Range("c5:c21")
If cell.Value = "n" Then cell.EntireRow.Hidden = True
Next cell
End If
If Range("C5").Value = "n" Then
For Each cell In Range("c6:c21")
If cell.Value = "n" Then cell.EntireRow.Hidden = True
Next cell
End If
If Range("C6").Value = "n" Then
For Each cell In Range("c7:c21")
If cell.Value = "n" Then cell.EntireRow.Hidden = True
Next cell
End If
If Range("C7").Value = "n" Then
For Each cell In Range("c8:c21")
If cell.Value = "n" Then cell.EntireRow.Hidden = True
Next cell
End If
If Range("C8").Value = "n" Then
For Each cell In Range("c9:c21")
If cell.Value = "n" Then cell.EntireRow.Hidden = True
Next cell
End If
If Range("C9").Value = "n" Then
For Each cell In Range("c10:c21")
If cell.Value = "n" Then cell.EntireRow.Hidden = True
Next cell
End If
If Range("C10").Value = "n" Then
For Each cell In Range("c11:c21")
If cell.Value = "n" Then cell.EntireRow.Hidden = True
Next cell
End If
If Range("C11").Value = "n" Then
For Each cell In Range("c12:c21")
If cell.Value = "n" Then cell.EntireRow.Hidden = True
Next cell
End If
If Range("C12").Value = "n" Then
For Each cell In Range("c13:c21")
If cell.Value = "n" Then cell.EntireRow.Hidden = True
Next cell
End If
If Range("C13").Value = "n" Then
For Each cell In Range("c14:c21")
If cell.Value = "n" Then cell.EntireRow.Hidden = True
Next cell
End If
If Range("C14").Value = "n" Then
For Each cell In Range("c15:c21")
If cell.Value = "n" Then cell.EntireRow.Hidden = True
Next cell
End If
If Range("C15").Value = "n" Then
For Each cell In Range("c16:c21")
If cell.Value = "n" Then cell.EntireRow.Hidden = True
Next cell
End If
If Range("C16").Value = "n" Then
For Each cell In Range("c17:c21")
If cell.Value = "n" Then cell.EntireRow.Hidden = True
Next cell
End If
If Range("C17").Value = "n" Then
For Each cell In Range("c18:c21")
If cell.Value = "n" Then cell.EntireRow.Hidden = True
Next cell
End If
If Range("C18").Value = "n" Then
For Each cell In Range("c19:c21")
If cell.Value = "n" Then cell.EntireRow.Hidden = True
Next cell
End If
If Range("C19").Value = "n" Then
For Each cell In Range("c20:c21")
If cell.Value = "n" Then cell.EntireRow.Hidden = True
Next cell
End If
If Range("C20").Value = "n" Then
For Each cell In Range("c21")
If cell.Value = "n" Then cell.EntireRow.Hidden = True
Next cell
End If
If Range("C21").Value = "n" Then
For Each cell In Range("c21")
If cell.Value = "n" Then cell.EntireRow.Hidden = True
Next cell
End If
End Sub

[Paul_Hossler]

Not tested since I didn't have your workbook to play with (#2 in my sig), but probably something like this

Not too sure if I got the ending cell correct




Option Explicit


Sub HideRows_Based_On_Values()
Dim r1 As Range, r2 As Range

For Each r1 In Range("C2:C21").Cells
If r1.Value = "n" Then
For Each r2 In Range(r1.Offset(1, 0), "C21").Cells
If r2.Value = "n" Then r2.EntireRow.Hidden = True
Next r2
End If
Next r1
End Sub

[Zack Barresse]

Duplicated http://www.vbaexpress.com/forum/showthread.php?70659-Show-Hide-ranges-with-button-selection

[Aussiebear]

There's something about the logic of the argument here that concerns me. As I currently understand your request, for each cell in Range C2:C20 if it contains the value "n" then hide the range defined by offsetting the active cell 1 row down to cell C21. Surely this should be a good example of "if Cell c2 value equals "n" do something else do something else", where the else part unhides the next cell.