Insert Column and add fields

pkew22

New Member
Joined
Aug 30, 2013
Messages
29
I have an Excel spreadsheet that where the headings start in row 5. Data starts in row 6.

The spreadsheet is updated frequently. The headings are always the same but the number of lines of data changes.

D5 = Units1 (field contains numbers or is blank)
E5 = Units2 (Field contains numbers or is blank)

I would like to insert a column to the right of Column E and add D + E in the inserted cell. I would like F5 to be called NewUnits.
Some cells in Columns D and E are blank. No cell in Column A is blank. (If cell A6 is the activecell and you press CTRL + Down Arrow Key -- you will get to the last cell with data in Col A. That is not the case if the activecell was E6 as there are blanks.)

I am using Excel 2010.

Please help with the code if you can.

Perry
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

royUK

Well-known Member
Joined
Jul 24, 2002
Messages
1,606
Have you formatted the data as a Table? You can then right click on a cell in Table choose Add Table Column. Add the formula to the first cell in that new column after the header, double click the right hand corner of the cell to copy the formula down. Tables will automatically insert formulas as the table expands

Excel Tables
 

pkew22

New Member
Joined
Aug 30, 2013
Messages
29
Thank you for the response.

I have not been using tables although I probably should have. As long as I was doing things manually, it took only a few seconds to do what I wanted. Now there has been a request to automate this as more people are going to be using this file.

I have automated a lot of the things most of which dealt with formatting some key rows. I was also able to insert the column. What I am unable to do, however, is to add the columns D and E if the data rows kept constantly changing.
 

royUK

Well-known Member
Joined
Jul 24, 2002
Messages
1,606
Is this what you are looking for

Code:
Dim Rw As Long
With Sheet1
Rw = .Cells(.Rows.Count, 1).End(xlUp).Row
.Range(.Cells(2, 6), .Cells(Rw, 6)).FormulaR1C1 = "=RC[-2]+RC[-1]"
End With
 

pkew22

New Member
Joined
Aug 30, 2013
Messages
29

ADVERTISEMENT

I apologize for bothering you one more time but could you (or anyone else) please help me with the following code:

Range("F5").Select
ActiveCell.FormulaR1C1 = "'Total Units"
Range("F6").Select
ActiveCell.FormulaR1C1 = "'=D6+E6"
Range("F6").Select
Selection.AutoFill Destination:=Range("F6:F237")

In the last line F6 is fixed but the F237 changes. I would like to base the last cell in Column F based on the number of rows in Column A. A is the only column that has data in every row.

Thanks again for your patience, time, and help.
 

royUK

Well-known Member
Joined
Jul 24, 2002
Messages
1,606
Have you tried my code? It adds the formula to column F

Code:
Dim Rw As LongWith Sheet1Rw = .Cells(.Rows.Count, 1).End(xlUp).Row.Cells(1,6".text=[FONT=Verdana] "'Total Units"[/FONT].Range(.Cells(2, 6), .Cells(Rw, 6)).FormulaR1C1 = "=RC[-2]+RC[-1]"End With[code]</pre>
 

Watch MrExcel Video

Forum statistics

Threads
1,129,268
Messages
5,635,180
Members
416,846
Latest member
ImGoing2needaFormula

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top