Prev: Collate Worksheets into single workbook
Next: page setup
From: K Dawson on 23 Mar 2010 10:55
I'm relatively new to VBA and need to create a macro to hide columns with
zero values. The workbook has 36 sheets, each sheet has columns B:CR and 202
rows. Row 202 is the sum of the respective column.
I'd like to run the macro in a separate workbook so I can use it for other
similar workbook situations.
Any suggestions would be greatly appreciated!
From: JLatham on 23 Mar 2010 14:31
I believe the code below will work for you. It will examine all used cells
on the row on a selected sheet where a cell is selected and hide all columns
with an empty cell or a zero value cell on that row. In your example, you'd
select any cell in row 202 of the sheet and then run the macro.

Sub HideZeroTotalColumns()
'this works with the active sheet
'in whatever book, and all you need
'to do is select the book, sheet and
'a cell in the "total" row to then
'hide all columns with a zero total

Dim anyWS As Worksheet
Dim anyTotalRow As Range
Dim anyCell As Range
Set anyWS = ActiveSheet
Set anyTotalRow = anyWS.Range(Cells(ActiveCell.Row, 1).Address, _
Cells(ActiveCell.Row, _
anyWS.Cells(ActiveCell.Row, Columns.Count). _
End(xlToLeft).Column))
'improve performance speed
Application.ScreenUpdating = False
'unhide in case was hidden and is now
'non-zero
anyTotalRow.Columns.EntireColumn.Hidden = False
For Each anyCell In anyTotalRow
If IsEmpty(anyCell) Or anyCell = 0 Then
anyCell.EntireColumn.Hidden = True
End If
Next
Set anyTotalRow = Nothing
Set anyWS = Nothing
End Sub


"K Dawson" wrote:

> I'm relatively new to VBA and need to create a macro to hide columns with
> zero values. The workbook has 36 sheets, each sheet has columns B:CR and 202
> rows. Row 202 is the sum of the respective column.
> I'd like to run the macro in a separate workbook so I can use it for other
> similar workbook situations.
> Any suggestions would be greatly appreciated!
 | 
Pages: 1
Prev: Collate Worksheets into single workbook
Next: page setup