Hidden Columns Based criteria in cell

[marreco]

I have a cell that want it monitored since the opening of the file is the B6

If B6 equal to 3, I would like to hide the Q column to column T

If B6 equal to 4 or would like to hide the columns U to X column

And so on, where it increases the value of B6 hide in every 4 columns

I need dinamic code.

How do it, without insert fomula in sheet. (my sheet has values in first columns)

Option Explicit
Sub HiddenColumns()
    Dim c As Integer
    Dim cel As Range
    Dim rng As Range

    Set rng = Range("D1:AWZ1")
    [Q1].Formula = "=INT((COLUMNS($A1:A1)-1)/4)+1"
    Range("Q1").AutoFill Destination:=Range(Cells(1, 17), Cells(1, 1300))
    Range("Q1").Resize(, 1300).Value = Range("Q1").Resize(, 1300).Value
    If [B6].Value <> "" Then
        c = [B6].Value - 2
        For Each cel In rng
            If cel.Value = c Then
                cel.EntireColumn.Hidden = True
            End If
        Next cel
    End If
    rng.ClearContents
End Sub

 

[NARAYANK991]

Hi ,

I am not sure I have understood you , but the following statement should do it :

ActiveSheet.Columns(([B6] - 1) * 4 + 9).Resize(, 4).EntireColumn.Hidden = True

Narayan

 

[marreco]

Hi Narayan, thanks, but what line i need change in my code?

What i want is....not use formula in cell, the code do it without use formula in cell.

 

[NARAYANK991]

Hi ,

You don't need to change any line ; only that one line will do the job ; your macro will therefore be :

Option Explicit

Sub HideColumns()
    ActiveSheet.Columns(([B6] - 1) * 4 + 9).Resize(, 4).EntireColumn.Hidden = True
End Sub

Narayan

 

[marreco]

What's going on....do you do it one line code...my god

Thank you very much!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!