VBA Code to Hide columns in Specific Intervals

[Zeema]

Hi,
I am working on a spreadsheet that spreads data till Column BVL in multiple tabs.
I am trying to create a macro to hide a range of columns in certain intervals, but failed to do so. I have tried the following code but it selects random selections and does not do what I want it to do.

Dim Mad As Variant
Dim sC As Variant
Dim eC As Variant
Dim X As Integer
X = InputBox("number of loops to run")
Mad = 1
sC = 17
eC = sC + 5
Do While (Mad < X)
ActiveWorkbook.Worksheets(MyWS).Activate
Range(Columns(sC), Columns(eC)).Select
Selection.EntireColumn.Hidden = True
Mad = Mad + 1
sC = sC + 9
eC = eC + 9
Loop

the following seems to work, but I cannot make it start from Column Q (Column # 17)

Dim MyWS As String
MyWS = "Test"

Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim colSize As Integer 'a block size
colSize = 9

For j = 1 To Columns.Count Step colSize
Range(Columns(j), Columns(colSize)).ColumnWidth = 5
Next j

'last column of each block
For i = 25 To Columns.Count Step colSize
Columns(i).ColumnWidth = 12
Next i

'the first column of each block
For k = 17 To Columns.Count Step colSize
Columns(k).ColumnWidth = 12
Next k
End Sub

I must have miss understood the above code... this is from another expert who helped in another thread here.

 

[jolivanes]

"hide a range of columns in certain intervals" What Range and what intervals?

 

[Zeema]

"hide a range of columns in certain intervals" What Range and what intervals?

I want to format column width like:
Column Q : columnwidth15
range ("R:W") hide
range ("X:Y): column width 12
and then repeat this pattern till column BLV.

But I want the formatting to start from column Q.
thanks a lot.

 

[jolivanes]

Try so.

Sub Like_So()
Dim i As Long
For i = 17 To 1686 Step 17
    With Cells(1, i)
        .EntireColumn.ColumnWidth = 15
            .Offset(, 1).Resize(, 6).EntireColumn.Hidden = True
        .Offset(, 7).Resize(, 2).EntireColumn.ColumnWidth = 12
    End With
Next i
End Sub

 

[Zeema]

Try so.

Sub Like_So()
Dim i As Long
For i = 17 To 1686 Step 17
    With Cells(1, i)
        .EntireColumn.ColumnWidth = 15
            .Offset(, 1).Resize(, 6).EntireColumn.Hidden = True
        .Offset(, 7).Resize(, 2).EntireColumn.ColumnWidth = 12
    End With
Next i
End Sub

thank you so much Jolivanes.
It worked partially. it did resize column Q:Z perfectly, but then it jumped to column AI (Colimn no. 35) instead of restarting from Column Z (Comuln no. 27).

also, can we try columnwidth=0 to hide the columns?
thank you so much!

 

[jolivanes]

Please verify that this request is not what you want
"and then repeat this pattern till column BLV."

Your change request starts at Column 17 so to my thinking, to stay with the pattern, the next is 17 + 17 = 34.
However, now you say Column 27 is the next start of the sequence. Now I wonder what the next sequence is.
Show us 3 or so consecutive blocks of ranges where you want changes made.
I have to say that I am a nosy type of person so please explain to me what the advantage is of setting Column Width to 0 (zero) versus Hiding Columns.
BTW, don't quote unless absolutely required. Use Post numbers and/or names instead.

Re: "restarting from Column Z (Comuln no. 27)."
On my computer, Column Z is not #27. The crashes I've had over the last while might have scrambled excel though.

 

[Zeema]

Sorry about mistyping the column number for column Z... I apologize.
the reason for asking to column width to "0" is because then the code will be easier to manipulate and visibly easier for checking.
below is the pattern sample. for better visibility instead of Column-Width=0, I Choose Column-Width=2.

Q column Width = 15 (the starting column or my pattern block)
R:W column width =2 (the columns I want hidden or column width 2 for testing)
X:Y column width = 12 (last two columns on my pattern block)

Z column Width = 15 (the starting column on my 2nd pattern block)
AA:AF (the 6 columns I want hidden in the pattern)
AG:AH (last two columns of my pattern block)

I want the pattern starting from Column Q and ending in column BVI.
Hope I explained better.
thank you again- really appreciate it.

 

[jolivanes]

Sub Or_Maybe_So()
Dim i As Long
    For i = 17 To 1936 Step 9
        With Cells(1, i)
            .ColumnWidth = 15
            .Offset(, 1).Resize(, 6).ColumnWidth = 2
            .Offset(, 7).Resize(, 2).ColumnWidth = 12
        End With
    Next i
End Sub

 

[Zeema]

Sub Or_Maybe_So()
Dim i As Long
    For i = 17 To 1936 Step 9
        With Cells(1, i)
            .ColumnWidth = 15
            .Offset(, 1).Resize(, 6).ColumnWidth = 2
            .Offset(, 7).Resize(, 2).ColumnWidth = 12
        End With
    Next i
End Sub

Thank you so, so, sooooooooo much!!! it works perfectly!!

 

[jolivanes]

Thanks for the update and good luck.