inserting new blank columns every nth column

[grantdowie]

Hello

I would like to add three new blank columns to an Excel spreadsheet using VBA after every 7 columns and I would very much appreciate any help to do this.

Thanks

Grant

 

[Fluff]

Hi & welcome to the board
Is this what you want?

Sub InsertColumns()

    Dim UsdCols As Long
    Dim Cnt As Long
    
    UsdCols = Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).column
    For Cnt = Int(UsdCols / 7) * 7 To 7 Step -7
        Columns(Cnt + 1).Resize(, 3).Insert
    Next Cnt
    
End Sub

 

[grantdowie]

Hello Fluff

Thanks ever so much. It worked a treat!

All the best

Grant

 

[Fluff]

Glad to help & thanks for the feedback

 

[grantdowie]

Hello Fluff

Sorry to trouble you again but I wondered whether you could help me further or direct me to a suitable article on the Forum.

Thanks to your excellent help I now have all my data in columns of varying row lengths separated by seven blank columns. Essentially I would like to populate these blank columns one by one via VBA by reference to this data. I would like to replicate the formula in each blank column cell for every eighth column cell until the end of the spreadsheet.

Thanks again.

All the best

Grant

 

[Fluff]

Something like this

Sub InsertFormula()

    Dim UsdCols As Long
    Dim Cnt As Long
    
    UsdCols = Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).column
    For Cnt = 8 To UsdCols Step 8
        Range(Cells(2, Cnt), Cells(Rows.Count, Cnt - 1).End(xlUp).Offset(, 1)).FormulaR1C1 = "=rc[-1]"
    Next Cnt
    
End Sub

 

[grantdowie]

Hello Fluff

Thanks very much for the new routine. I think I am doing something wrong in running it though. For example I was trying to replicate the formula stored in D10 along the columns and I am not sure how I tell the spreadsheet how to do that via your routine.

All the best

Grant

 

[Fluff]

This will take the formula in D2 & copy it into columns 8,16,24 etc from row2 to the last used cell in the previous column.

Sub InsertFormula()

    Dim UsdCols As Long
    Dim Cnt As Long
    
    UsdCols = Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).column
    For Cnt = 8 To UsdCols Step 8
        Range(Cells(2, Cnt), Cells(Rows.Count, Cnt - 1).End(xlUp).Offset(, 1)).Formula = Range("D2").Formula
    Next Cnt
    
End Sub

 

[mikelee]

Hi & welcome to the board
Is this what you want?

Sub InsertColumns()

    Dim UsdCols As Long
    Dim Cnt As Long
   
    UsdCols = Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).column
    For Cnt = Int(UsdCols / 7) * 7 To 7 Step -7
        Columns(Cnt + 1).Resize(, 3).Insert
    Next Cnt
   
End Sub

Thank you Fluff. I was able to use these codes for my project.

 

[Fluff]

Glad it helped & thanks for the feedback.