Consulting

Results 1 to 7 of 7

Thread: Possible to clean up my formula

  1. #1
    VBAX Newbie
    Joined
    Jul 2019
    Posts
    2
    Location

    Possible to clean up my formula

    Hey people,

    Simple formula I have working for me when I run a query on a data sheet. The rows appear when a result is matched in the category.

    It is quite repetitive and I find messy. Is it possible to simplify and clean it up ?

    Thanks!!

    ---

    Private Sub Worksheet_Change(ByVal Target As Range)
    
    
        If Range("A9").Value = "" Then
            Rows("5:11").EntireRow.Hidden = True
        ElseIf Range("A9").Value <> "" Then
            Rows("5:11").EntireRow.Hidden = False
        End If
        
        If Range("A17").Value = "" Then
            Rows("13:19").EntireRow.Hidden = True
        ElseIf Range("A17").Value <> "" Then
            Rows("13:19").EntireRow.Hidden = False
        End If
        
        If Range("A25").Value = "" Then
            Rows("21:27").EntireRow.Hidden = True
        ElseIf Range("A25").Value <> "" Then
            Rows("21:27").EntireRow.Hidden = False
        End If
        
        If Range("A33").Value = "" Then
            Rows("29:33").EntireRow.Hidden = True
        ElseIf Range("A33").Value <> "" Then
            Rows("29:33").EntireRow.Hidden = False
        End If
            
        If Range("A39").Value = "" Then
            Rows("35:41").EntireRow.Hidden = True
        ElseIf Range("A39").Value <> "" Then
            Rows("35:41").EntireRow.Hidden = False
        End If
            
        If Range("A47").Value = "" Then
            Rows("43:49").EntireRow.Hidden = True
        ElseIf Range("A47").Value <> "" Then
            Rows("43:49").EntireRow.Hidden = False
        End If
    
    
        If Range("A55").Value = "" Then
            Rows("51:57").EntireRow.Hidden = True
        ElseIf Range("A55").Value <> "" Then
            Rows("51:57").EntireRow.Hidden = False
        End If
            
        If Range("A63").Value = "" Then
            Rows("59:65").EntireRow.Hidden = True
        ElseIf Range("A63").Value <> "" Then
            Rows("59:65").EntireRow.Hidden = False
        End If
            
        If Range("A71").Value = "" Then
            Rows("67:73").EntireRow.Hidden = True
        ElseIf Range("A71").Value <> "" Then
            Rows("67:73").EntireRow.Hidden = False
        End If
        
    End Sub
    Last edited by Aussiebear; 07-25-2019 at 12:27 AM. Reason: Added code tags to submitted code

  2. #2
    VBAX Wizard
    Joined
    Apr 2007
    Posts
    6,937
    Location
    Not tested

    Rows("5:11").Hidden = (Len(Range("A9").Value) = 0)
    Repeat
    Paul

    ------------------------------------------------------------------------------------------------------------------------
    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s)
    (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  3. #3
    VBAX Expert Leith Ross's Avatar
    Joined
    Oct 2012
    Location
    San Francisco, California
    Posts
    544
    Location
    Hello fan22,

    Here is way to shorten your code...

    Private Sub Worksheet_Change(ByVal Target As Range)
            
        Dim r As Long
        
            If Target.Cells.Count > 1 Then Exit Sub
            If Target.Row <> 1 Then Exit Sub
    
    
            Me.UsedRange.Rows.Hidden = False
            
            Application.EnableEvents = False
            
            For r = 9 To 71 Step 8
                If Cells(r, "A") = "" Then
                    Range(Rows(r - 4), Rows(r + 2)).Hidden = True
                End If
            Next r
            
           Application.EnableEvents = True
    
    
    End Sub
    Sincerely,
    Leith Ross

    "1N73LL1G3NC3 15 7H3 4B1L17Y 70 4D4P7 70 CH4NG3 - 573PH3N H4WK1NG"

  4. #4
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    25,145
    Location
    Shouldn't you be testing that Target, the cell being changed, is one that you want to trigger all of that row hiding, otherwise why do it on a change event at all?
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  5. #5
    VBAX Newbie
    Joined
    Jul 2019
    Posts
    2
    Location
    Thanks for your responses, but it doesn't work in my situation. I'll try to explain better what's going on in the workbook.

    **I don't have the Power Query function and can't have access to it since administrators blocked our system from downloading additional software.

    1) I search the clients ID in "E3", to search all rows in Column A of 9 Sheets where all ID's are identified.

    Rows 5:11 is a table representing "Year 1", with the ID cell in "A9", containing the formula below.

    =IFERROR(INDEX('Sheet1'!A:A,MATCH($E$3,'Sheet1'!$A:$A,0)),"")

    - All other cells with information on this client also feeds into the range with a similar formula

    Rows 13:19 with formula for "A17"

    =IFERROR(INDEX('Sheet2'!A:A,MATCH($E$3,'Sheet2'!$A:$A,0)),"")

    etc...

    21:27 - A25

    29:33 - A33

    35:41 - A39

    43:49 - A47

    51:57 - A55

    59:65 - A63

    67:73 - A71

    ----

    I need all these ranges hidden until an ID is identified in the targeted cells which will unhide the range.
    - One ID can match multiple "years"

    Let me know if you have any questions

    Thanks again!

  6. #6
    VBAX Wizard
    Joined
    Apr 2007
    Posts
    6,937
    Location
    Lots of questions

    Didn't really understand your 'example'

    Several people answered the question asked, so if possilbe attach a representive workbook with no sensitive data and a detailed explaination of what you'd like
    Paul

    ------------------------------------------------------------------------------------------------------------------------
    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s)
    (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  7. #7
    Knowledge Base Approver VBAX Guru
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    4,841
    try:
    Private Sub Worksheet_Change(ByVal Target As Range)
    With Range("A5:A11,A13:A19,A21:A27,A29:A33,A35:A41,A43:A49,A51:A57,A59:A65,A67:A73")'note these areas are not the same size.
      .EntireRow.Hidden = True
      For Each are In .Areas
        If are.Cells(5).Value <> "" Then are.EntireRow.Hidden = False
      Next are
    End With
    End Sub
    p45cal - - - - -This is my signature, it appears after all my posts. Below is not directed at anyone in particular - so don't take offence! - (You might guess why it's there though)
    If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

Posting Permissions

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