0

My code adds one column after each existing column.

I need to add 14 columns. I want this to start by adding the columns after column 2 for each column with data. I believe my current code covers that.

Dim z As Integer
Columns(2).Select
For z = 2 To 20
    ActiveCell.EntireColumn.Insert
    ActiveCell.Offset(0, 2).Select
Next z
Community
  • 1
  • 1
Matt
  • 1
  • Hi, Welcome.... Your code inserts 19 columns.. As you have selected second column First column is inserted between A and B. Now if you want to insert first columns between B and C then Select third column first. and then Z from 4 to 17..Oh you mean 14 columns each time after col B? – Naresh Mar 12 '20 at 17:35
  • the help on changing first column between B and C is helpful but yes i need to insert 14 columns each time, any ideas? – Matt Mar 12 '20 at 17:45
  • Answer added... – Naresh Mar 12 '20 at 17:46

2 Answers2

2

Loop backwards, and no need to Select.

This doesn't check if there is any data in the column.

Sub x()

Dim z As Long

For z = 20 To 2 Step -1
  Columns(z).Resize(, 14).Insert
Next z

End Sub
SJR
  • 22,986
  • 6
  • 18
  • 26
  • You can use SJR's code its more efficient just change it to For z = 21 To 3 Step -1 ... My answer is also edited – Naresh Mar 12 '20 at 17:54
0

Welcome.... Your code inserts 19 columns.. As you have selected second column First column is inserted between A and B. Now if you want to insert first columns between B and C then Select third column first. and then Z from 4 to 17..Oh you mean 14 columns each time after col B? Then...

Dim z As Integer
  Columns(3).Select
  For z = 4 To 22
    Range(ActiveCell, ActiveCell.Offset(0, 13)).EntireColumn.Insert
    'ActiveCell.EntireColumn.Insert
    ActiveCell.End(xlToRight).Offset(0, 1).Select
    'ActiveCell.Offset(0, 2).Select
  Next z
Naresh
  • 2,984
  • 2
  • 9
  • 15
  • So this would add 14 columns between each existing, do i need to change the offset if im doing this 19 times – Matt Mar 12 '20 at 17:48
  • You can use SJR's code its more efficient just change it to `For z = 21 To 3 Step -1` ... My answer is also edited – Naresh Mar 12 '20 at 17:51
  • Naresh - suggest you read https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba – SJR Mar 12 '20 at 17:57
  • 1
    @SJR .. yes,, thats why I suggested the user to use your code. Its more efficient... Thanks for the link. – Naresh Mar 12 '20 at 17:58