Printable View
Hi Everyone,
Need you support to solve my issue and reduce the manual work through VBA.
I have big sheet have more that 300 columns (all the cells contain formulas), some columns are have values and some have zero values (but it changeable next day),
I need the VBA to search if the column has zero value and take action to hide that column. and if possible to unhide them if they have values.
Thanks
So if Sum of a column = 0, hide the column?
Column headers are first row and data starts on second row?
Check out https://stackoverflow.com/questions/...at-are-empty-0
You can use a VBA macro to hide columns in an Excel worksheet that contain only zero values and unhide them when they have non-zero values. The code loops through each column, checks for non-zero values, and hides or unhides accordingly. To implement, open the VBA editor, insert a new module, and paste the provided code, then run the macro.Quote:
Originally Posted by DJBoY
Paste the following into a regular module :
Create a command button that is attached to the above macro.Code:Option Explicit
'If the entire Column is empty :
Sub HideCol()
Dim N As Long, wf As WorksheetFunction, M As Long
Dim i As Long, j As Long
N = Columns.Count
M = Rows.Count
Set wf = Application.WorksheetFunction
Application.ScreenUpdating = False
For i = 26 To 1 Step -1 '<<<---------------------------- Change 26 (Col Z) to reflect max columns possible
If wf.CountBlank(Columns(i)) <> M Then Exit For
Next i
For j = i To 1 Step -1
If wf.CountBlank(Columns(j)) = M Then
Cells(1, j).EntireColumn.Hidden = True
End If
Next j
Application.ScreenUpdating = True
End Sub
Do you mean if the sum of the column = 0 or if one of the entries is 0?Quote:
Originally Posted by DJBoY
So col A = 1,2,3,-3,-2,-1 sum = 0
or
Col A = 1,2,0,3,2,1 contains 0
You can use the following VBA code to hide columns with zero values and show them again when there are other values.Quote:
Originally Posted by DJBoY
@Sophian, there is no code in your post.
OP hasn't responded on this 9-month old post, so likely has abandoned.