PDA

View Full Version : Sleeper: Best way to add a column to a range with offset of n



JKwan
04-06-2016, 06:22 PM
I am wondering what is the best / easiest way to add a column to a range with offset of every n columns then give a header to those columns? I am thinking of using a loop thru the columns of my range then go back and add the header to all those newly added columns. Is that a good approach?

My range is from column L to FA and wanted to insert a column starting with column N then every 5 columns

Thanks.

snb
04-07-2016, 01:10 AM
I'd prefer to move data instead of 'inserting' columns (no matter how hard you 'insert' the number of columns.count remains unaltered).

e.g.


Sub M_snb()
sn = Cells(1, 13).CurrentRegion.Resize(, 146)

sp = Split(Join([transpose(row(1:145)&if(mod(row(1:145),5)=1," 146",""))]))
sq = Application.Index(sn, Evaluate("row(1:" & UBound(sn) & ")"), sp)

Cells(5, 13).Resize(UBound(sq), UBound(sq, 2)) = sq
End Sub