How do I insert columns dynamically in Excel?

Question

I would like to insert separating columns into an Excel report to make the existing columns easier to view.

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

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

So if ival=10 then the columns 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, ... and N&O.

This may be a possibility for inserting columns: Workbooks("yourworkbook").Worksheets("theworksheet").Columns(i).Insert

But I'm not sure how to loop through ival.

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

TylerH · Accepted Answer · 2018-01-18 17:25:18Z

9

The below code should work without needing to worry about ival:

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

edited Jan 18, 2018 at 17:25

TylerH

21.3k84 gold badges84 silver badges121 bronze badges

answered Jun 5, 2013 at 3:58

Santosh

12.4k4 gold badges43 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jon Crowell · Accepted Answer · 2013-06-05 04:14:08Z

3

Try this:

Sub InsertSeparatorColumns()
    Dim ws as Worksheet
    Dim firstCol As String
    Dim lastRow As Long
    Dim i As Long
    Dim howManySeparators As Long

    Set ws = ThisWorkbook.Sheets("Sheet1")
    firstCol = "F"
    lastRow = ws.Range("D" & ws.Rows.Count).End(xlUp).Row
    howManySeparators = Application.WorksheetFunction.CountIf _
                            (ws.range("D2:D" & LastRow), "Other")

    For i = 1 To howManySeparators * 2 Step 2
        ws.Range(firstCol & 1).Offset(, i).EntireColumn.Insert
    Next i
End Sub

edited Jun 5, 2013 at 4:14

answered Jun 5, 2013 at 1:29

Jon Crowell

22.4k15 gold badges94 silver badges113 bronze badges

Comments

n a · Accepted Answer · 2023-12-21 02:34:10Z

0

For the case to insert a column at the left of only specified cells,

- original cells (insert the columns at once)

- after insertion

- code

Sub fixColumn()
    Dim idx As Integer, cellStr As String
    With ActiveSheet
        For idx = Columns("G").Column To Columns("O").Column
            cellStr = cellStr & .Cells(1, idx).Address(False, False) & ","
        Next
        cellStr = Left(cellStr, Len(cellStr) - 1)
        .Range(cellStr).Select
        Selection.EntireColumn.Insert , CopyOrigin:=xlFormatFromLeftOrAbove
    End With
End Sub

answered Dec 21, 2023 at 2:34

n a

1634 bronze badges

Collectives™ on Stack Overflow

How do I insert columns dynamically in Excel?

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related