Hide columns base on text

[Panoos64]

Hi all, i would to provide me support so that, when i enter in cell "I4" the text "MONTHLY" should hide automatically the entire columns "D" and "E" and when i change the text to "YEARLY" should unhide the columns. Thank you all in advance

 

[Panoos64]

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!

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

 

[Fluff]

You cannot put one sub into another like.
How are you calling that sub?

 

[Panoos64]

Sub SumColumnsCM(),
How can i achieve so that the above codes to be in one "Sub" and run both of them together?

 

[Fluff]

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

 

[Panoos64]

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!

 

[Fluff]

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?

 

[Panoos64]

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

 

[Fluff]

In that case try

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

 

[Panoos64]

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

 

[Fluff]

You're welcome & thanks for the feedback