How hide all Columns based on specific cell data

12 views
Skip to first unread message

Dilip Ghosh

unread,
Mar 15, 2021, 5:55:11 AM3/15/21
to
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 ?




Auric__

unread,
Mar 20, 2021, 3:57:02 AM3/20/21
to
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.
Reply all
Reply to author
Forward
0 new messages