+ Reply to Thread
Results 1 to 5 of 5

Hide rows macro not working properly

Hybrid View

lgastal Hide rows macro not working... 07-09-2012, 04:37 AM
vikas.bhandari Re: Hide rows macro not... 07-09-2012, 04:42 AM
lgastal Re: Hide rows macro not... 07-09-2012, 04:50 AM
vikas.bhandari Re: Hide rows macro not... 07-09-2012, 05:09 AM
lgastal Re: Hide rows macro not... 07-09-2012, 11:43 PM
  1. #1
    Registered User
    Join Date
    07-09-2012
    Location
    Singapore
    MS-Off Ver
    Excel 2010
    Posts
    3

    Question Hide rows macro not working properly

    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

  2. #2
    Forum Contributor vikas.bhandari's Avatar
    Join Date
    04-07-2006
    Location
    Delhi, India
    MS-Off Ver
    Office 2007 and 2010
    Posts
    303

    Re: Hide rows macro not working properly

    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

  3. #3
    Registered User
    Join Date
    07-09-2012
    Location
    Singapore
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Hide rows macro not working properly

    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
    Last edited by lgastal; 07-09-2012 at 05:02 AM. Reason: add dummy file

  4. #4
    Forum Contributor vikas.bhandari's Avatar
    Join Date
    04-07-2006
    Location
    Delhi, India
    MS-Off Ver
    Office 2007 and 2010
    Posts
    303

    Re: Hide rows macro not working properly

    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

  5. #5
    Registered User
    Join Date
    07-09-2012
    Location
    Singapore
    MS-Off Ver
    Excel 2010
    Posts
    3

    Thumbs up Re: Hide rows macro not working properly

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1