Search code examples
excelvbahideshow

Controlling (hiding, showing) columns with VBA based on other columns structure

I can't figure out the right logic for the VBA to set (hide or show) the columns in some area (columns K to O) based on the "template" area (columns C to G). In the example below, I hid the column D and I would like to run a code that would scan what columns in range C3 to G3 are hidden or shown and accordingly (show or hide columns) edit the columns in range K3 to O3, which in this example means hiding the column L. I can work with ranges and can further edit the code for my purposes, but I just don't know how to somehow save the column structure in the template and replicate it in the second area. Also, there will be formulas in the template and nulls in the edited area, but I think I can do this adjustment on my own. I will be grateful for any idea.

Example of columns

Solution

  • Try the next code, please:

    Sub testHideUnhide()
 Dim sh As Worksheet, rng As Range, cel As Range
 
 Set sh = ActiveSheet 'use here your sheet, if not the active one
 Set rng = sh.Range("C3:G3")
 For Each cel In rng.cells
    cel.Offset(0, 9).EntireColumn.Hidden = cel.EntireColumn.Hidden
 Next

    It will hide and unhide the columns in rage "K:O" according to "C:G" situation, from this point of view.