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

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.

thanks in advance.
0
MarkVrenken
Asked:
MarkVrenken
  • 4
  • 4
1 Solution
 
NorieCommented:
What will be in this column?
0
 
MarkVrenkenAuthor 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
 
NorieCommented:
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
MarkVrenkenAuthor 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
 
NorieCommented:
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

Open in new window

0
 
MarkVrenkenAuthor 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

Open in new window

0
 
NorieCommented:
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

Open in new window

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
 
MarkVrenkenAuthor 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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