Insert Column and add fields

[pkew22]

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

 

[royUK]

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]

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]

Is this what you are looking for

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]

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]

Have you tried my code? It adds the formula to column F

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>

 

[pkew22]

Thank you for your help. This resolves the issue.