CODEWITHSUNDEEP
excelvba
Khalil Salame
Khalil Salame

Reputation: 13

How to loop through columns a given number of times

I have my top row filled with values. I would like to insert two columns between each of these values, until the last non-empty cell in my top row.

I'm stuck there in my code:

Sub AddCol()
'
' AddCol Macro
'

For i = 1 To 2

    Sheets("CashFlow").Select
    Columns(i + 1).Select
    Selection.Insert Shift:=x1ToRight, CopyOrigin:=xlFormatFromLeftOrAbove

Next i

End Sub

Upvotes: 1

Views: 63

Answers (3)

JohnyL
JohnyL

Reputation: 7142

A shorter way:

.Columns(i + 1).Resize(, 2).Insert Shift:=x1ToRight, CopyOrigin:=xlFormatFromLeftOrAbove

Upvotes: 0

DDV
DDV

Reputation: 2384

A couple things.

a) You need to get a value for the number of columns so you can loop through and add

b) When looping, loop from highest to lowest because you are adding columns if you loop lowest to highest it will effect you counters and you will end up not looping through the entire header set.

See below code:

Sub ColumnsAdd()

    Dim lCol As Long
    Dim i As Long
    Dim myWS As Worksheet

    Set myWS = Sheets("CashFlow")

    lCol = myWS.Cells(1, myWS.Columns.Count).End(xlToLeft).Column

    For i = lCol To 1 Step -1
        myWS.Columns(i).Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
        myWS.Columns(i).Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Next i

End Sub

Upvotes: 2

Mikku
Mikku

Reputation: 6654

This will Work For you:

Sub AddCol() ' ' AddCol Macro '

With Thisworkbook.Worksheets("CashFlow")

For i = 1 To .Cells(1, Columns.Count).End(xlToLeft).Column * 3 Step 3


    .Columns(i + 1).Insert Shift:=x1ToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    .Columns(i + 1).Insert Shift:=x1ToRight, CopyOrigin:=xlFormatFromLeftOrAbove

Next i

End With    

End Sub

Upvotes: 1

Related Questions