Insert Columns Dynamically vba

Question

I have a Report a part of it is hard to read and I would like to insert separating columns to make it easier to view.

The report is created dynamically and I never know how many columns there will be 5, 10, 17...

The section starts at F and goes to ival=Application.WorksheetFunction.CountIf(range("D2:D" & LastRow), "Other")

So if ival=10 then the colunms are F G H I J K L M N O and I need to insert columns between F&G G&H H&I I&J ... N&O

This is maybe a possibility for inserting columns Workbooks("yourworkbook").Worksheets("theworksheet").Columns(i).Insert

But not shure how to loop through ival, been trying but no luck.

Thanks

Sub InsertColumns()
Dim iVal As Integer
Dim Rng As range
Dim LastRow As Long
Dim i  As Integer

With Sheets("sheet1")
    LastRow = .range("D" & .Rows.Count).End(xlUp).Row
End With

iVal = Application.WorksheetFunction.CountIf(range("D2:D" & LastRow), "Other")

For i = 7 To iVal - 1
Workbooks("yourworkbook").Worksheets("theworksheet").Columns(i+1).Insert
Next i

End Sub

Accepted Answer

Try below code :

Sub InsertSeparatorColumns()

Dim lastCol As Long

With Sheets("sheet1")
        lastCol = Cells(2, .Columns.Count).End(xlToLeft).Column

For i = lastCol To 7 Step -1
            .Columns(i).Insert
            .Columns(i).ColumnWidth = 0.5
        Next

End With

End Sub

Question posted 2013 · +7 upvotes