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.
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.