Hide Columns VBA if sum of Column is x

hmltnangel

Board Regular
Joined
Aug 25, 2010
Messages
221
Office Version
  1. 2016
Platform
  1. Windows
Hi All, wouold really appreciate some help on this one.

I use the code below to bring back some information from another worksheet. It works great. However Can I get the code an extra piece on the end, to hide specific columns if specific cells in that column are zero?

VBA Code:
Sub ListMembers()
   Dim Cl As Range
   Dim Ary As Variant
   Dim i As Long
   
On Error GoTo ErrMsg
  
   With Sheets("Roster").ListObjects("Roster")
      ReDim Ary(1 To .ListRows.Count, 1 To 1)
      .Range.AutoFilter .ListColumns("Manager Emplid").Index, Sheets("Skills Matrix-rating template").Range("C4")
      For Each Cl In .ListColumns("Emplid").DataBodyRange.SpecialCells(xlVisible)
         i = i + 1
         Ary(i, 1) = Cl.Value
         i = i + 1
      Next Cl
   End With
   Sheets("Skills Matrix-rating template").Range("B11").Resize(i).Value = Ary
   
ErrMsg:
             MsgBox ("Not a Manager or Manager has no Direct Reports")
             
End Sub

In Column F11:F60, every second cell is equals "Baseline", (so F11, F13 etc etc). What I want is for say, columns G:CA to hide if the total of the values in hose corresponding columns for Baseline is zero. So, if G11, G13,G15,G17 etc etc total zero, then hide column G.

Hope I am explaining this right, but happy to expand :)
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
13,621
Office Version
  1. 2007
Platform
  1. Windows
Try this:

VBA Code:
  Dim cad As String
  With Sheets("Skills Matrix-rating template")
    For j = Columns("G").Column To Columns("CA").Column
      cad = ""
      For k = 11 To 60 Step 2
        If Not IsError(.Cells(k, j)) Then
          cad = cad & .Cells(k, j).Value
        End If
      Next
      .Columns(j).Hidden = cad = ""
    Next
  End With
 
Solution

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

hmltnangel

Board Regular
Joined
Aug 25, 2010
Messages
221
Office Version
  1. 2016
Platform
  1. Windows
Thanks, took some time to get back around to doing the changes, but this worked perfect Dante!
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
13,621
Office Version
  1. 2007
Platform
  1. Windows
Again with pleasure. Thanks for the feedback
 

Forum statistics

Threads
1,141,619
Messages
5,707,445
Members
421,508
Latest member
someinternetuser

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top