Insert a column every X columns

**ExCeline** · 11-25-2009, 08:51 AM

Hi everybody,

I am looking for a VBA code (or alternatively a way of doing this in normal Excel) that can let me insert a column every X columns (for example every six columns).

Ideally I would want a code that can let me insert a specific column (the same) every X columns, but a formula that inserts a blank column every X columns will hopefully do.

Thanks in advance for taking time out of your day to help me out!

-ExCeline

**rwgrietveld** · 11-25-2009, 09:27 AM

a simple interactive approach

Sub insRow()

Dim FirstRow As Long, Every As Long, Times As Long
Dim RowStr As String

FirstRow = InputBox("Select first row", "Row Selection")
Every = InputBox("How many rows must be inserted?", "# rows")
Times = InputBox("How many times", "# times")

For x = (FirstRow + (Times - 1) * Every) To FirstRow Step -Every
  RowStr = CStr(x) & ":" & CStr(x + Every - 1)
  Rows(RowStr).Insert
Next x

End Sub

**martindwilson** · 11-25-2009, 09:53 AM

sort approach
in a spare row
number columns 1 thro end say 1 to 40 wher 40 is your last column with data so say you want to insert at every 5th column
in col 41 put =5*(columns($a$1:a1)+0.5 and drag across
copy that whole row paste special values
now select your data choose sort /options left to right chose the row you have the numbers in and sort by that
columns will be sorted 1 ,2 ,3 ,4 ,4.5 ,5 ,6 ,7 ,8 ,8.5......... ie all the x.5 will be empty columns
then delete spare row.

**ExCeline** · 11-25-2009, 12:15 PM

Thanks for the swift replies, guys!

I've tried both methods, but unfortunately not quite managed to solve the problem yet. As far as I can tell, the VBA-formula inserts a row every other row. I can live with the fact that it pertains to rows and not colums by transposing the data. However, I am still not able to make it insert a row every x rows, only every second one.

As for the simple sorting approach, it seems very promising. However, I am not able to get the 1, 2, 3, 4, 5, 5.5, 6, 7, 8, 9, 10, 10.5, 11 row through the formula you provided, Martin. As far as I can tell, =5*(columns($a$1:a1)+0.5) turns into 5.5, 10.5, 15.5, 20.5 etc.

I'm sorry to ask you guys for a clarification on what is undoubtedly pretty basic stuff. I've really tried my best to make the formulas work, and will continue to do so, but any pointers you can give would be greatly appreciated!

-ExCeline

**ExCeline** · 11-25-2009, 12:51 PM

Ok, I feel kinda silly asking the question and solving it myself right afterwards. I managed to put a new variable into the formula that controlled jumps and thus got a nice blank row every X rows.

The only issue remaining is how I paste the same values in all the empty rows without pasting over the cells in my other rows. If anyone has a tip, I wouldn't mind hearing it

-ExCeline

**ExCeline** · 11-25-2009, 01:26 PM

Turns out I found a kind of ad hoc way of solving my final problem as well. Thanks again for the help, guys! You really put me on the right track.

-ExCeline