Insert new colums after each one existing column

[Ralph]

Hi All,

I have a spreadsheet spanning 500 rows for which I would like to add a
number of columns after each exisiting column. Say the current sheet looks
like A,B,C; I would want it to look like A,a1,a2,a3,B,b1,b2,b3,C,c1,c2,c3,
Where the lowecase colums followed by a number are the colums that are to be
inserted. Since the sheet comprises of 500 columns, I suspect there is a
faster way than doing this manually, I just don't know how to. Could you help
me out?

Best regards,

Ralph

 

[Rob Wills]

500 columns?!?!?

Are you getting your Rows and Columns confused?

I'm assuming you mean rows, but his can be adapted

Dim i As Integer

Application.ScreenUpdating = False 'Used to increase performance

For i = 500 To 1 Step -1
Range("A" & i).EntireRow.Insert
Range("A" & i).EntireRow.Insert
Range("A" & i).EntireRow.Insert
Next i
Application.ScreenUpdating = True


========================

 

[Rick Rothstein]

You mixed your column and row references in your description... I'll assume
you meant columns as you subject indicates. I used Const statements to
define the parameters for the insertions (I set them to your example
request)... just change them as needed.

Sub InsertColumns()
Dim X As Long, Z As Long
Const BeginCol As Long = 1 ' Column A
Const EndCol As Long = 2 ' Column C
Const NumColToInsert As Long = 3 ' Insert 3 columns
For X = EndCol To BeginCol Step -1
For Z = 1 To NumColToInsert
Columns(X).Offset(, 1).Insert
Next
Next
End Sub

 

[Ralph]

Hi Rob,

Thank you for taking the time to help me with this.

I am talking about columns. I have placed all the S&P 500 companies in a
worksheet and would like to have amongst others, price data for these
companies in rows. Luckily Excel 2007 supports more than the standard 256
columns.

How would I change the code? replace EntireRow with EntireColumn?

Best regards,

Ralph

 

[Rick Rothstein]

You didn't like the code I posted for some reason?

 

[Gary Keramidas]

i don't think they didn't like it. i think they don't see it. i've noticed so
many people pay attention to one thread and don't notice somebody else
responded.

 

[Rob Wills]

I liked it ;-)

(beat mine hands down)

You didn't like the code I posted for some reason?

 

[Ralph]

Hi Rick,

I didn't see your post until now (using the webversion at the microsoft
website to browse the newsgroups). Thank you for your help!

Regards,

Ralph

 

[Rick Rothstein]

You are welcome. I don't use the web version, but I think someone once
mentioned that you need to do a refresh (not sure if that was a browser
refresh or some kind of webpage refresh) in order to see the newly arrived
responses since you opened the page. If that works, then you should do it
often when you post a message so you get to see all the responses to your
question... otherwise you might miss the perfect answer to your question
(not that I am saying my answer was perfect by any means... I'm just making
a point).