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.

 

[Zeema]

I am trying to delete few columns in a pattern.
the pattern is a set of 9 columns; first set is from column Q:Y and my data range spreads till column BVI.
I want to keep the first two columns with column with 15 and delete the next 7 columns from each set.

I have tried the following, but it is extremely slow.
Sub DeleteColumnSlow()
If (MsgBox("Ready to delete columns?", vbYesNo) = vbYes) Then
Dim X As Integer
Dim Mad As Variant
Dim srtC As Variant
Dim EndC As Variant

X = InputBox("number of loops to run")

Mad = 1
srtR = 9
srtC = 19
EndC = 100

Do While (Mad < X)
Set MR = Range(Cells(srtR, srtC), Cells(srtR, EndC))
For Each cell In MR
If cell.Value = "Delete" Then cell.EntireColumn.Delete
Next

Mad = Mad + 1
Loop
MsgBox "update complete, Check updates."
Else: MsgBox "complete sample formatting and then run update"
End If

End Sub


Any help is greatly appreciated.
thanks
Zeema

 

[jolivanes]

Can you be so kind and explain the logic of changing Column Widths of about 1900 Columns in blocks of 9 Columns followed by deleting 7 Columns in these blacks.
Why change them in the first place?