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 :)
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
13,617
Office Version
  1. 2007
Platform
  1. Windows
Add the following to the end of your code and test.

VBA Code:
  Dim i As Long, j As Long
  Dim s As Double
  
  For j = Columns("G").Column To Columns("CA").Column
    s = 0
    For i = 11 To 60 Step 2
      s = s + Cells(i, j).Value
    Next
    Columns(j).Hidden = s = 0
  Next
 

hmltnangel

Board Regular
Joined
Aug 25, 2010
Messages
221
Office Version
  1. 2016
Platform
  1. Windows
Thanks Dante,

I get "Duplicate Declaration in Current Scope" Error when I add to the end

Maybe I am placing it wrongly?
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
13,617
Office Version
  1. 2007
Platform
  1. Windows
I guess you want to hide the columns in sheet "Skills Matrix-rating template"

Try this:

VBA Code:
Sub ListMembers()
   Dim Cl As Range
   Dim Ary As Variant
   Dim i As Long
   Dim k As Long, j As Long
   Dim s As Double
   
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
  
  With Sheets("Skills Matrix-rating template")
    For j = Columns("G").Column To Columns("CA").Column
      s = 0
      For k = 11 To 60 Step 2
        s = s + .Cells(k, j).Value
      Next
      .Columns(j).Hidden = s = 0
    Next
  End With
ErrMsg:
             
  MsgBox ("Not a Manager or Manager has no Direct Reports")

End Sub
 

hmltnangel

Board Regular
Joined
Aug 25, 2010
Messages
221
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Thanks, I had almost the same after I had fiddled with it. ( I forgot to add the With/End With lines for the extra code.

However, Now its been tidied, thanks, I kept getting the "error" messagebox. So, I stripped the On error segment out to see where it was having the issue, and its this line thats causing it

VBA Code:
        s = s + .Cells(k, j).Value
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
13,617
Office Version
  1. 2007
Platform
  1. Windows
.Cells(k, j).Value
If in that cell you have error or text then you will not be able to do the sum. You should validate the cell with something like this:

It is not recommended to use the On Error statement, it is recommended to try to verify possible errors with code.
VBA Code:
      For k = 11 To 60 Step 2
        If Not IsError(.Cells(k, j)) Then
          If IsNumeric(.Cells(k, j).Value) Then
            s = s + .Cells(k, j).Value
          End If
        End If
      Next
 

hmltnangel

Board Regular
Joined
Aug 25, 2010
Messages
221
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Works perfect now Dante. Thanks for your help :)
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
13,617
Office Version
  1. 2007
Platform
  1. Windows
I'm glad to help you. Thanks for the feedback.
 

hmltnangel

Board Regular
Joined
Aug 25, 2010
Messages
221
Office Version
  1. 2016
Platform
  1. Windows
Dante, if you're still watching ....

I had to make a change whereby the cells dont equal "zero" - they are completely blank. Now the VBA hides the entire set of columns instead of the ones with just the zeros
 

hmltnangel

Board Regular
Joined
Aug 25, 2010
Messages
221
Office Version
  1. 2016
Platform
  1. Windows
Actually ignore my previous message.

Whats happening is as follows:

Previously I was using a number in the cells - which if the total in the cells was zero then hide the column. This worked perfect. Now I would like to use letters instead. Is that possible.
 

Forum statistics

Threads
1,141,614
Messages
5,707,410
Members
421,508
Latest member
Jalayne

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