Hide rows macro not working properly

**lgastal** · 07-09-2012, 04:37 AM

Hi, I am having an issue with my macro where the hide rows formula works for 1 tab but not another -

The below script is not working
Sub Hide_PA()
'
' Hide/Unhide Port Agency Sheet

If Range("L1") = False Then
Sheets("2013 Budget").Select
Application.Goto Reference:="PAr"
Selection.EntireRow.Hidden = True
Application.Goto Reference:="PAs"
Selection.EntireRow.Hidden = True
Sheets("Overview").Select
Range("A1").Select
Else
Sheets("2013 Budget").Select
Application.Goto Reference:="PAr"
Selection.EntireRow.Hidden = False
Application.Goto Reference:="PAs"
Selection.EntireRow.Hidden = False
Sheets("Overview").Select
Range("A1").Select
End If

End Sub

but next section does?
Sub Hide_SPA()
'
' Hide/Unhide Port Agency Strategy Sheet

If Range("K1") = False Then
Sheets("2014-2015 Strategy Plan").Select
Application.Goto Reference:="SPAr"
Selection.EntireRow.Hidden = True
Application.Goto Reference:="SPAs"
Selection.EntireRow.Hidden = True
Sheets("Overview").Select
Range("A1").Select
Else
Sheets("2014-2015 Strategy Plan").Select
Application.Goto Reference:="SPAr"
Selection.EntireRow.Hidden = False
Application.Goto Reference:="SPAs"
Selection.EntireRow.Hidden = False
Sheets("Overview").Select
Range("A1").Select
End If

End Sub

I have named the rows to hide as 2 separate named ranges for the 2 rows on each tab but it won't hide on the first tab.

Any help on resolving this would be greatly appreciated.
Thanks

**vikas.bhandari** · 07-09-2012, 04:42 AM

First thing:

Please always use Code Tags whenever you post any codes in the thread.

Secondly, if the same code is working for one tab, and not working for another tab. It definitely means there is a logical flaw with the way you have designed your spreadsheets.

My possible guesses:

1. Range("L1") is always true so it is not hiding it.
2. Range("PAr") is residing somewhere else than expected.

If both of the above is incorrect, then please post the spreadsheet with non-confidential data or with dummy values so that we can try to find the solution.

Thanks,
Vikas B

**lgastal** · 07-09-2012, 04:50 AM

Hi Vikas,
Thanks for your comments. I am not sure what you mean by Code Tags - could you please explain.

To add some further detail to my situation
I have an overview tab with select boxes to business streams ie: Port Agency
When you de-select the box a field on this overview tab changes from True to False
So in my macro, what I want it to do is when this field has been un-checked I want it to hide the related "Port Agency" rows on the 2 tabs or when it is checked then to show it.
To try to simplify the range that needs hiding I gave the "Port Agency" rows a named range ie: PAr and PAs and SPAr and SPAs
The macro looks identical to me for both tabs but it doesn't hide on the Budget tab.

Thanks,
LG

**vikas.bhandari** · 07-09-2012, 05:09 AM

I am going to add the following code in a Code Tag, it is mentioned in the reply window with a Hash(#) Sign.

Try this:

Sheets("2013 Budget").Range("PAr").EntireRow.Hidden = not(Range("L1").Value)

Let me know if any error is thrown or works just fine. I still believe that your box is not linked to "L1" so L1 is never true. Can you please recheck if the box is changing value in L1 or any other cell.

Thanks,
Vikas B

**lgastal** · 07-09-2012, 11:43 PM

Hi Vikas,

Thanks. I have worked out what the issue is. It is not the macro but a group of merged cells on the Budget tab.
Whenever the macro tries to hide the 1 row it can't due to the merged cells in column AA6-15 and AA29-29 on this tab. I have unmerged the cells and the macro runs fine.

Thanks,
LGastal