Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Easier way to insert columns
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Easier way to insert columns
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" -- HTH... Jim Thomlinson "sanj" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Easier way to insert columns
Thanks Jim!
"Jim Thomlinson" wrote in message ... 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" -- HTH... Jim Thomlinson "sanj" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Easier way to insert columns
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 "sanj" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Easier way to insert columns
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 "sanj" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Easier way to insert columns
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" wrote in message ... 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 "sanj" wrote in message ... 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Easier way to insert columns
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 "sanj" wrote in message ... 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" wrote in message ... 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 "sanj" wrote in message ... 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Easier way to insert columns
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" wrote in message ... 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 "sanj" wrote in message ... 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" wrote in message ... 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 "sanj" wrote in message ... 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Easier way to insert columns
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 "sanj" wrote in message ... 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" wrote in message ... 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 "sanj" wrote in message ... 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" wrote in message ... 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 "sanj" wrote in message ... 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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Easier way to insert columns
Norman,
Thats perfect and will save me alot of time (as well as being educated!) Thanks! Sanjay "Norman Jones" wrote in message ... 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 "sanj" wrote in message ... 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" wrote in message ... 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 "sanj" wrote in message ... 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" wrote in message ... 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 "sanj" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I insert columns of dates in between exsisting columns of | Excel Discussion (Misc queries) | |||
can no longer insert new columns & unhide hidden columns | Excel Worksheet Functions | |||
unable to insert columns in excel, insert- columns (disabled) | Excel Discussion (Misc queries) | |||
Excel should allow you to create borders around columns easier | Excel Discussion (Misc queries) | |||
insert columns macro is putting 2 columns instead of 1 | Excel Worksheet Functions |