How hide all Columns based on specific cell data
Dilip Ghosh wrote:
I want to hide all the columns of a Spreadsheet based on specific data
on a cell.
For example, if B1=0 and E1=0, hide column B and E.
Can anyone help about the easiest way to do it in Excel ?
Has to be done in VBA. It's easy if the cells to be checked are in the same
row:
Const rowChk = 1
For c = 1 To ActiveCell.SpecialCells(xlCellTypeLastCell).Column
If Cells(rowChk, c).Value = 0 Then Cells(1, c).EntireColumn.Hidden = True
Next c
(Edit rowChk to the row number to check, of course.)
If the cells aren't in the same row, or are otherwise non-contiguous in any
way, and aren't very numerous, you could use an array:
chk = Array("A1", "B2", "C4")
For c = 0 To UBound(chk)
If Range(chk(c)).Value = 0 Then Range(chk(c)).EntireColumn.Hidden = True
Next c
You could do it other ways, too:
For Each c In Range("A1:D1,C2:E9")
If c.Value = 0 Then c.EntireColumn.Hidden = True
Next
--
A resignation is not a negotiable document.
|