asked on

Excel VBA: insert empty columns among non empty columns

Hello experts,

I am looking for a procedure in order to insert multiple non empty column among non empty columns the requirement is the following:

1-Loop the various active columns
2-Inputbox insert the number of columns that you want to insert between empty columns.
3-Insert empty columns
4-Take as a reference the header of left column for inserted column

Example: Empty columns have been inserted for column A C E, left header has been parse for inserted column.

II attached dummy file.

Thank you in advance for your help.
Insert-empty-columns.xlsx

Norie

Not 100% sure what you are after, a before/after would have been useful, but try this.

Sub InsertEmptyColumns()
Dim NoCols As Long
Dim Col As Long

    NoCols = InputBox("How many empty columns do you want to insert?")
    
    Col = Cells(1, Columns.Count).End(xlToLeft).Column
    
    Do
        Columns(Col).Offset(, 1).Resize(, NoCols).EntireColumn.Insert
        Cells(1, Col).Resize(, NoCols + 1).Value = Cells(1, Col)
        Col = Col - 1
    Loop Until Col < 1
        
End Sub

Open in new window

Luis Diaz

ASKER

Please find attached dummy file with Input and Output sheet;

The proposed procedure doesn't fill header name with "New" & left column header as reported in output sheet in highlighted in gree.

Thank you very much for your help.
Insert-columns-V2.xlsx

Norie

There was no mention of 'New' in the original question.:)

I'll take a look at the attachment though.

ASKER CERTIFIED SOLUTION

Norie

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Theo Kouwenhoven

Is the standard Exel option not enough?

Select column A,B and C and choose (right-button) insert, 3 columns inserted (A is moved to D)

Luis Diaz

ASKER

@Norie: Thank you for this proposal. Possible to add green field at new headers inserted?
Thank you in advance for your help.

Luis Diaz

ASKER

I was able to performed last requirement by adding line 12 of Nories's proposal. Thank you all.

Sub Insert_Empty_Cols()
Dim NoCols As Long
Dim Col As Long

    NoCols = InputBox("How many empty columns do you want to insert?")
    
    Col = Cells(1, Columns.Count).End(xlToLeft).Column
    
    Do
        Columns(Col).offset(, 1).Resize(, NoCols).EntireColumn.Insert
        Cells(1, Col).offset(, 1).Resize(, NoCols).Value = "New_" & Cells(1, Col).Value
        Cells(1, Col).offset(, 1).Resize(, NoCols).Interior.Color = RGB(98, 244, 66)
        
        Col = Col - 1
    Loop Until Col < 1
        
End Sub

Open in new window