Page 2 of 2 FirstFirst 12
Results 11 to 20 of 20

Thread: Hide columns base on text

  1. #11
    Board Regular
    Join Date
    Mar 2014
    Location
    cyprus
    Posts
    729
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Hide columns base on text

    Hi Fluff, my previous command/code is below, by which it prepares the totals for a range of columns "C:M" and i wanted below the mentioned code and before "End Sub" to paste your code so that to run once for both commands. Is there a way to do it? Please see below exactly what i am looking for. Thank you so much!




    Code:
    lastRow = Range("A" & Rows.Count).End(xlUp).Row
    Range("A" & lastRow + 2) = "Total"
    Range("A" & lastRow + 3) = "Grand Total"
    
    Range("C" & lastRow + 2) = Application.WorksheetFunction.Sum(Range("C8:C" & lastRow))
    Range("C" & lastRow + 3) = Application.WorksheetFunction.Sum(Range("C8:C" & lastRow))
    
    Range("D" & lastRow + 2) = Application.WorksheetFunction.Sum(Range("D8:D" & lastRow))
    Range("D" & lastRow + 3) = Application.WorksheetFunction.Sum(Range("D8:D" & lastRow))
    
    Range("E" & lastRow + 2) = Application.WorksheetFunction.Sum(Range("E8:E" & lastRow))
    Range("E" & lastRow + 3) = Application.WorksheetFunction.Sum(Range("E8:E" & lastRow))
    
    Range("F" & lastRow + 2) = Application.WorksheetFunction.Sum(Range("F8:F" & lastRow))
    Range("F" & lastRow + 3) = Application.WorksheetFunction.Sum(Range("F8:F" & lastRow))
    
    Range("G" & lastRow + 2) = Application.WorksheetFunction.Sum(Range("G8:G" & lastRow))
    Range("G" & lastRow + 3) = Application.WorksheetFunction.Sum(Range("G8:G" & lastRow))
    
    Range("H" & lastRow + 2) = Application.WorksheetFunction.Sum(Range("H8:H" & lastRow))
    Range("H" & lastRow + 3) = Application.WorksheetFunction.Sum(Range("H8:H" & lastRow))
    
    Range("I" & lastRow + 2) = Application.WorksheetFunction.Sum(Range("I8:I" & lastRow))
    Range("I" & lastRow + 3) = Application.WorksheetFunction.Sum(Range("I8:I" & lastRow))
    
    Range("J" & lastRow + 2) = Application.WorksheetFunction.Sum(Range("J8:J" & lastRow))
    Range("J" & lastRow + 3) = Application.WorksheetFunction.Sum(Range("J8:J" & lastRow))
    
    Range("K" & lastRow + 2) = Application.WorksheetFunction.Sum(Range("K8:K" & lastRow))
    Range("K" & lastRow + 3) = Application.WorksheetFunction.Sum(Range("K8:K" & lastRow))
        
    Range("L" & lastRow + 2) = Application.WorksheetFunction.Sum(Range("L8:L" & lastRow))
    Range("L" & lastRow + 3) = Application.WorksheetFunction.Sum(Range("L8:L" & lastRow))
      
    Range("M" & lastRow + 2) = Application.WorksheetFunction.Sum(Range("M8:M" & lastRow))
    Range("M" & lastRow + 3) = Application.WorksheetFunction.Sum(Range("M8:M" & lastRow))
        
    
    Private Sub Worksheet_Change(ByVal Target As Range)
       If Target.CountLarge > 1 Then Exit Sub
       If Target.Address(0, 0) = "I4" Then
          Range("D:E").EntireColumn.Hidden = Target = "MONTHLY"
       End If
    End Sub
    Last edited by Fluff; Jun 25th, 2019 at 08:12 AM. Reason: Added code tags

  2. #12
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    24,132
    Post Thanks / Like
    Mentioned
    411 Post(s)
    Tagged
    43 Thread(s)

    Default Re: Hide columns base on text

    You cannot put one sub into another like.
    How are you calling that sub?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #13
    Board Regular
    Join Date
    Mar 2014
    Location
    cyprus
    Posts
    729
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Hide columns base on text

    Sub SumColumnsCM(),
    How can i achieve so that the above codes to be in one "Sub" and run both of them together?
    Last edited by Panoos64; Jun 25th, 2019 at 08:39 AM.

  4. #14
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    24,132
    Post Thanks / Like
    Mentioned
    411 Post(s)
    Tagged
    43 Thread(s)

    Default Re: Hide columns base on text

    If you want to hide the columns automatically, as per your op, then you cannot combine those codes, unless you want both of them to fire whenever you change I4
    - 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. #15
    Board Regular
    Join Date
    Mar 2014
    Location
    cyprus
    Posts
    729
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Hide columns base on text

    Your code it works but i do not want to run it as a separate module. Is there any other way by which when i enter the text "MONTHLY" in "I4" to hide the mentioned columns and when i enter "YEARLY" to reappears or retrieve them. Is not necessary to be VBA. e.g. by "Drop Down List", or "Tick Box" or "Combo Box" e.t.c. Thank you once again. Hv a great day!

  6. #16
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    24,132
    Post Thanks / Like
    Mentioned
    411 Post(s)
    Tagged
    43 Thread(s)

    Default Re: Hide columns base on text

    The only way to have columns auto hide/unhide is through VBA & it will need need an Event.
    Do you want your code to run every time I4 is changed?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  7. #17
    Board Regular
    Join Date
    Mar 2014
    Location
    cyprus
    Posts
    729
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Hide columns base on text

    Yeap Fluff! When i change the "I4" cell, should run the code. Thanks!

  8. #18
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    24,132
    Post Thanks / Like
    Mentioned
    411 Post(s)
    Tagged
    43 Thread(s)

    Default Re: Hide columns base on text

    In that case try
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
       If Target.CountLarge > 1 Then Exit Sub
       If Target.Address(0, 0) = "I4" Then
          
          Application.EnableEvents = False
          Range("D:E").EntireColumn.Hidden = Target = "MONTHLY"
          Lastrow = Range("A" & Rows.Count).End(xlUp).Row
          Range("A" & Lastrow + 2) = "Total"
          Range("A" & Lastrow + 3) = "Grand Total"
          
          Range("C" & Lastrow + 2) = Application.WorksheetFunction.Sum(Range("C8:C" & Lastrow))
          Range("C" & Lastrow + 3) = Application.WorksheetFunction.Sum(Range("C8:C" & Lastrow))
          
          Range("D" & Lastrow + 2) = Application.WorksheetFunction.Sum(Range("D8:D" & Lastrow))
          Range("D" & Lastrow + 3) = Application.WorksheetFunction.Sum(Range("D8:D" & Lastrow))
          
          Range("E" & Lastrow + 2) = Application.WorksheetFunction.Sum(Range("E8:E" & Lastrow))
          Range("E" & Lastrow + 3) = Application.WorksheetFunction.Sum(Range("E8:E" & Lastrow))
          
          Range("F" & Lastrow + 2) = Application.WorksheetFunction.Sum(Range("F8:F" & Lastrow))
          Range("F" & Lastrow + 3) = Application.WorksheetFunction.Sum(Range("F8:F" & Lastrow))
          
          Range("G" & Lastrow + 2) = Application.WorksheetFunction.Sum(Range("G8:G" & Lastrow))
          Range("G" & Lastrow + 3) = Application.WorksheetFunction.Sum(Range("G8:G" & Lastrow))
          
          Range("H" & Lastrow + 2) = Application.WorksheetFunction.Sum(Range("H8:H" & Lastrow))
          Range("H" & Lastrow + 3) = Application.WorksheetFunction.Sum(Range("H8:H" & Lastrow))
          
          Range("I" & Lastrow + 2) = Application.WorksheetFunction.Sum(Range("I8:I" & Lastrow))
          Range("I" & Lastrow + 3) = Application.WorksheetFunction.Sum(Range("I8:I" & Lastrow))
          
          Range("J" & Lastrow + 2) = Application.WorksheetFunction.Sum(Range("J8:J" & Lastrow))
          Range("J" & Lastrow + 3) = Application.WorksheetFunction.Sum(Range("J8:J" & Lastrow))
          
          Range("K" & Lastrow + 2) = Application.WorksheetFunction.Sum(Range("K8:K" & Lastrow))
          Range("K" & Lastrow + 3) = Application.WorksheetFunction.Sum(Range("K8:K" & Lastrow))
              
          Range("L" & Lastrow + 2) = Application.WorksheetFunction.Sum(Range("L8:L" & Lastrow))
          Range("L" & Lastrow + 3) = Application.WorksheetFunction.Sum(Range("L8:L" & Lastrow))
            
          Range("M" & Lastrow + 2) = Application.WorksheetFunction.Sum(Range("M8:M" & Lastrow))
          Range("M" & Lastrow + 3) = Application.WorksheetFunction.Sum(Range("M8:M" & Lastrow))
          Application.EnableEvents = True
       End If
    End Sub
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  9. #19
    Board Regular
    Join Date
    Mar 2014
    Location
    cyprus
    Posts
    729
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Hide columns base on text

    Thank you Fluff, it is okay now! I appreciate for all work you done for me. Best wishes!

  10. #20
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    24,132
    Post Thanks / Like
    Mentioned
    411 Post(s)
    Tagged
    43 Thread(s)

    Default Re: Hide columns base on text

    You're welcome & thanks for the feedback
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

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
  •