Results 1 to 5 of 5

Thread: VBA code to hide a range of cells based on cell value
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Sep 2019
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA code to hide a range of cells based on cell value

    Hi,
    I am not sure if i am close but i am attempting to hide a range of rows based on the value of another cell. To be more clear A11 value is "Status" and i need to evaluate B11 and if it is "Open" then hide 11 rows up. This repeats in a dynamically lengthed document that i run weekly. Below is what i have tried - i am sure it is not as efficient as it could be but it is a starting point. Any help or suggestions would be greatly appreciated.

    Code:
    Sub Closed_Susp()
        Dim Rng As Range
        Dim WorkRng As Range
        On Error Resume Next
        xTitleID = "Status Tool"
        Set WorkRng = Application.Selection
        Set WorkRng = Application.InputBox("Range", xTitleID, WorkRng.Address, Type:=8)
        Set WorkRng = WorkRng.Columns(1)
        xLastRow = WorkRng.Rows.Count
        Application.ScreenUpdating = False
        For xRowIndex = xLastRow To 1 Step -1
            Set Rng = WorkRng.Range("A" & xRowIndex)
            If Rng.Value = "Status:" And Rng.Offset(0, 1).Value = "OPEN" Then
                Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(-11, 0)).EntireRow.Hidden = True
            End If
        Next
        Application.ScreenUpdating = True
    End Sub
    Thank you!!

  2. #2
    Administrator starl's Avatar
    Join Date
    Aug 2002
    Location
    Everywhere!
    Posts
    5,859
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA code to hide a range of cells based on cell value

    The major issue is if your selection happens to be above row 11, you'll crash since you can't hide rows that don't exist (well, I guess you won't because you have the resume next, but that's not true error handling). ie, the Status and Open are in row 10, you try to hide a row above row 1, which does not exist. Or even if the row is 11, then you're trying to hide row 0... so you might want to ensure the selection is below row 11.

    Also, your string comparisons are case dependent. So if the value in B is "Open" or "open" - it won't match.

    else, sure, code is rough, but we all start somewhere.

  3. #3
    New Member
    Join Date
    Sep 2019
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA code to hide a range of cells based on cell value

    @starl thank you for your reply. This is from a generated report so there will always be a minimum of 11 lines above the Status line, and also the status value is generated so it will have the same capitalization (a good point out however). With all that in mind, my problem is that for some reason the code above does not actually hide anything - and that is my issue.

    I assumed it was something in my code that i am missing, bad argument or a misunderstanding of the application of the operators. Any thoughts would be greatly appreciated.

    Thank you for your time and help!

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    27,958
    Post Thanks / Like
    Mentioned
    467 Post(s)
    Tagged
    47 Thread(s)

    Default Re: VBA code to hide a range of cells based on cell value

    What is the exact value in col A?
    "Status", or "Status:" or maybe "Status: something"
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  5. #5
    Administrator starl's Avatar
    Join Date
    Aug 2002
    Location
    Everywhere!
    Posts
    5,859
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA code to hide a range of cells based on cell value

    Your code is fine and works (in a perfectly setup workbook). But, like Fluff pointed out - Excel makes EXACT comparisons. Even a space can throw of the comparison. Step through your code, when it reaches a line you KNOW is correct, watch the variables, check the logic and see what's throwing it off.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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