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

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


========================
"Ralph" wrote:

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

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

--
Rick (MVP - Excel)


"Ralph" wrote in message
...
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

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

"Rob Wills" wrote:

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


========================
"Ralph" wrote:

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

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

--
Rick (MVP - Excel)


"Ralph" wrote in message
...
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

"Rob Wills" wrote:

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


========================
"Ralph" wrote:

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

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.

--


Gary

"Rick Rothstein" wrote in message
...
You didn't like the code I posted for some reason?

--
Rick (MVP - Excel)


"Ralph" wrote in message
...
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

"Rob Wills" wrote:

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


========================
"Ralph" wrote:

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

I liked it ;-)

(beat mine hands down)

"Rick Rothstein" wrote:

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

--
Rick (MVP - Excel)


"Ralph" wrote in message
...
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

"Rob Wills" wrote:

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


========================
"Ralph" wrote:

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

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" wrote:

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

--
Rick (MVP - Excel)


"Ralph" wrote in message
...
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

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).

--
Rick (MVP - Excel)


"Ralph" wrote in message
...
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" wrote:

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

--
Rick (MVP - Excel)


"Ralph" wrote in message
...
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