• Status: Solved
• Priority: Medium
• Security: Public
• Views: 955

# Excel vba( add new column after the last column)

Dear experts,

i want to add a column after the last filled column in excel using vba. There must be an easy way to do this i guess. so just a new column.

0
MarkVrenken
• 4
• 4
1 Solution

Commented:
What will be in this column?
0

Author Commented:
percentage_owned
Unique_number
Name_Of_Office

0
0
0
137,999
40,488
0
3,796
55,751
45,188
37,327
114,201
53,018
125,935
182,869
87,720
62,840
10,124
38
5,388
22,904
26,726
32,547
121,661
100,338
172,366
161,774
120,536
80,270
7,705
0
1,294
8,544
10,142
24,325
104,470
81,863
164,381
157,435
108,354
81,138
8,518
419
1,746
9,445
11,606
20,676
80,908
78,021
14,099
15,105
101,932
85,232
1,100
33
2,991
1,583
1,333
2,133
7,525
6,134

something like this:)
0

Commented:
Sorry but I don't quite follow, where will these values come from?

Are they calculations based on the data in the rest of the columns (or elsewhere)?
0

Author Commented:
they are copied from another row and i do some calculations in my vba. Ill try to explain. I have a company with these numbers. but actualy this company has two subsidiaries and both have a certain share in these number. suppose one has 40 procent production the other 60. so the new columns will be: office I
rows 5 to 64 * 0,4
and the other: office II
will be rows 5 to 64 times 0,6

hope i explain it correct
0

Commented:
I think you need to explain the layout of your data.

What columns do you already have?

Which of those columns, if any, can be used for the calculation(s) for the new column?

Here's a very simple example of how to add a column with formulas after the last column.
``````Dim NextCol As Long
Dim LastRow As Long

With Worksheets("Sheet1")
NextCol = .Cells(1, Columns.Count).End(xlToLeft).Column+1
LastRow = .Cells(Rows.Count, 1).End(xlUp).Row
.Cells(2,NextCol).Resize(LastRow-1).Formula = "=A2/F2+K2"
End With
``````
0

Author Commented:
i just was thinking about a for loop and do the calculations in vba. so i just need to use the values. and then paste the calculations as values in the new column. thanks for your help so instead i will have then somethin like

``````dim newCol as Long
dim oldCol as long

Old col = A

with Worksheets("sheet1")

newCol = .Cells(1, Columns.Count).End(xlToLeft).Column+1

for i = 5 to 64

newCol & i = oldcol & i * 0,40

next i
``````
0

Commented:
If you do the calculations in VBA then the code will take much longer.

If you use VBA to put the formula in you can then convert them to value.
``````Dim NextCol As Long
Dim LastRow As Long

With Worksheets("Sheet1")
NextCol = .Cells(1, Columns.Count).End(xlToLeft).Column+1
LastRow = .Cells(Rows.Count, 1).End(xlUp).Row
With .Cells(2,NextCol).Resize(LastRow-1)
.Formula = "=A2/F2+K2"
.Value = .Value
End With
End With
``````
Obviously you would need to change the formula but from what you've posted that shouldn't be too hard as it's just a simple multiplication.
0

Author Commented:
Yes, thats true:) I just wanted to know how to add a new column the rest i already had an idea of how to do it:) thanks and have good day:)
0

## Featured Post

• 4
• 4
Tackle projects and never again get stuck behind a technical roadblock.