VBA Code to Hide columns in Specific Intervals

Zeema

New Member
Joined
Jan 29, 2024
Messages
10
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
"hide a range of columns in certain intervals" What Range and what intervals?
 
Upvote 0
"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.
 
Upvote 0
Try so.
Code:
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
 
Upvote 0
Try so.
Code:
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!
 
Upvote 0
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.
 
Last edited:
Upvote 0
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.

1706563178519.png
 
Upvote 0
Code:
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
 
Upvote 0
Solution
Code:
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!!
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,103
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top