Easier way to insert columns

[sanj]

Hi,

I need to insert 40-50 columns and label the headers, at the moment I am
using the following code in a macro:

Columns("A:A").Select
Selection.Insert Shift:=xlToRight
Range("A1").Select
ActiveCell.FormulaR1C1 = "Status"
Columns("C:C").Select
Selection.Insert Shift:=xlToRight
Range("C1").Select
ActiveCell.FormulaR1C1 = "Details"

etc

Is there an easier way to write this?

Thanks!

Excel 2002

 

[Guest]

You can get rid of the selects...

Columns("A:A").Insert Shift:=xlToRight
Range("A1").Formula = "Status"
Columns("C:C").Insert Shift:=xlToRight
Range("C1").Formula = "Details"

 

[sanj]

Thanks Jim!

You can get rid of the selects...

Columns("A:A").Insert Shift:=xlToRight
Range("A1").Formula = "Status"
Columns("C:C").Insert Shift:=xlToRight
Range("C1").Formula = "Details"

 

[John]

Maybe overkill but this might help if you need to run it again on other
worksheets/books:

Best regards

John

Sub Check()

Dim sLabel As String

Columns("A:AN").Select '40 columns wide
Selection.Insert Shift:=xlToRight
Range("A1").Select

For x = 1 To 40
Select Case x
Case 1
sLabel = "Status"
Case 2
sLabel = "Details"
Case 3
sLabel = "Something Else...."
'Keep adding your labels here 1 to 40
End Select

Application.ActiveWorkbook.ActiveSheet.Cells(1, x).FormulaR1C1 =
sLabel
sLabel = ""
Next x
End Sub

 

[Norman Jones]

Hi Sanj.

As another suggestion:

Sub TestIt2()
Dim i As Long, j As Long
Dim arr As Variant

arr = Array("Status", "Details", "Header3", "Header4", "Header5", _
"Header6", "Header7", "Header8", "Header9", "Header10", _
"Header11", "Header12", "Header13", "Header14", "Header15", _
"Header16", "Header17", "Header18", "Header19", "Header20") '... etc

For i = 1 To (UBound(arr) - LBound(arr) + 1) * 2 Step 2
j = j + 1
Columns(i).Insert
Columns(i).Cells(1).Value = arr(j - 1)
Next

End Sub

 

[sanj]

Thanks Norman,

where do I define where I need to the columns to be inserted i.e. Status
needs to be inserted in Column A, the Details need to be the header for a
new column inserted into column C etc

Regards,

Sanj

 

[Norman Jones]

Hi Sanji,

where do I define where I need to the columns to be inserted i.e. Status
needs to be inserted in Column A, the Details need to be the header for a
new column inserted into column C etc

That is all done in my code.

Try the code on a copy of your workbook to see and test.

All you need to do, is to replace the Header3, Header4...Header20... values
with your intended header values.

a column will be inserted for each header value that you supply.

 

[sanj]

Thanks Norman,

I can see how this works, the column is inserted after every other columns,
however, I need the columns to be inserted at particular points such as
A, C, H, I, J AA, AF etc is it possible to add another array for where the
columns need to be inserted or a multidimensional array

arr = Array([A, "Status"], [C, "Details"], [H, "Header3"], [I,
"Header4"}

Regards,

Sanj

 

[Norman Jones]

Hi Sanj,

I need the columns to be inserted at particular points such as
A, C, H, I, J AA, AF etc

Try:

Sub TestIt2A()
Dim i As Long, j As Long
Dim arr As Variant
Dim arr2 As Variant

arr = Array("Status", "Details", "Header3", "Header4", "Header5", _
"Header6", "Header7", "Header8", "Header9", "Header10", _
"Header11", "Header12", "Header13", "Header14", "Header15", _
"Header16", "Header17", "Header18", "Header19", "Header20") '... etc

arr2 = Array("A", "C", "H", "I", "J", "AA", "AF") '... etc

For i = 1 To (UBound(arr2) - LBound(arr2) + 1) Step 1
Columns(arr2(i - 1)).Insert
Columns(arr2(i - 1)).Cells(1).Value = arr(i - 1)
Next

End Sub


---
Regards,
Norman

Thanks Norman,

I can see how this works, the column is inserted after every other
columns,
however, I need the columns to be inserted at particular points such as
A, C, H, I, J AA, AF etc is it possible to add another array for where the
columns need to be inserted or a multidimensional array

arr = Array([A, "Status"], [C, "Details"], [H, "Header3"], [I,
"Header4"}

Regards,

Sanj

 

[sanj]

Norman,

Thats perfect and will save me alot of time (as well as being educated!)

Thanks!

Sanjay

Hi Sanj,

I need the columns to be inserted at particular points such as
A, C, H, I, J AA, AF etc

Try:

Sub TestIt2A()
Dim i As Long, j As Long
Dim arr As Variant
Dim arr2 As Variant

arr = Array("Status", "Details", "Header3", "Header4", "Header5", _
"Header6", "Header7", "Header8", "Header9", "Header10", _
"Header11", "Header12", "Header13", "Header14", "Header15", _
"Header16", "Header17", "Header18", "Header19", "Header20") '... etc

arr2 = Array("A", "C", "H", "I", "J", "AA", "AF") '... etc

For i = 1 To (UBound(arr2) - LBound(arr2) + 1) Step 1
Columns(arr2(i - 1)).Insert
Columns(arr2(i - 1)).Cells(1).Value = arr(i - 1)
Next

End Sub


---
Regards,
Norman

Thanks Norman,

I can see how this works, the column is inserted after every other
columns,
however, I need the columns to be inserted at particular points such as
A, C, H, I, J AA, AF etc is it possible to add another array for where the
columns need to be inserted or a multidimensional array

arr = Array([A, "Status"], [C, "Details"], [H, "Header3"], [I,
"Header4"}

Regards,

Sanj

Hi Sanji,

where do I define where I need to the columns to be inserted i.e.
Status
needs to be inserted in Column A, the Details need to be the header

for
a

new column inserted into column C etc


That is all done in my code.

Try the code on a copy of your workbook to see and test.

All you need to do, is to replace the Header3, Header4...Header20... values
with your intended header values.

a column will be inserted for each header value that you supply.


---
Regards,
Norman



Thanks Norman,

where do I define where I need to the columns to be inserted i.e.
Status
needs to be inserted in Column A, the Details need to be the header

for
a

new column inserted into column C etc

Regards,

Sanj



Hi Sanj.

As another suggestion:

Sub TestIt2()
Dim i As Long, j As Long
Dim arr As Variant

arr = Array("Status", "Details", "Header3", "Header4", "Header5",
_
"Header6", "Header7", "Header8", "Header9", "Header10", _
"Header11", "Header12", "Header13", "Header14", "Header15", _
"Header16", "Header17", "Header18", "Header19", "Header20") '...
etc

For i = 1 To (UBound(arr) - LBound(arr) + 1) * 2 Step 2
j = j + 1
Columns(i).Insert
Columns(i).Cells(1).Value = arr(j - 1)
Next

End Sub

---
Regards,
Norman



Hi,

I need to insert 40-50 columns and label the headers, at the moment
I
am
using the following code in a macro:

Columns("A:A").Select
Selection.Insert Shift:=xlToRight
Range("A1").Select
ActiveCell.FormulaR1C1 = "Status"
Columns("C:C").Select
Selection.Insert Shift:=xlToRight
Range("C1").Select
ActiveCell.FormulaR1C1 = "Details"

etc

Is there an easier way to write this?

Thanks!

Excel 2002