Solved Add Formula after the Last used Cell in a Row

Microsoft Excel 2010 - complete product...
December 16, 2014 at 06:06:10
Specs: Windows 7 Service Pack 1

I am looking to add various formulas in the last column of a row. The code is below what I am Iooking to do is replace column "F" with the last cell in the row.

Sub SumAcross()
    ActiveCell.FormulaR1C1 = "=SUM(RC[-4]:RC[-1])"
    Selection.AutoFill Destination:=Range("F6:F29"), Type:=xlFillDefault
    Selection.NumberFormat = "$#,##0.00"
    ActiveCell.FormulaR1C1 = "=(R[-1]C-R[-2]C)/ABS(R[-1]C)"
    ActiveCell.FormulaR1C1 = "=AVERAGE(RC[-4]:RC[-1])"
    Selection.AutoFill Destination:=Range("F31:F32"), Type:=xlFillDefault
    ActiveWindow.SmallScroll Down:=-3
    ActiveCell.FormulaR1C1 = "Grand Total"
    ActiveCell.FormulaR1C1 = "Totals"
    ActiveCell.FormulaR1C1 = "Total"
End Sub

Thank you,

See More: Add Formula after the Last used Cell in a Row

Report •

December 16, 2014 at 10:10:37
✔ Best Answer
First, it appears that you have recorded this code. The VBA recorder creates code that is bloated and inefficient. It’s perfectly OK to use the VBA recorder, but you should clean up the code afterwards so that is it easier to follow and runs more efficiently.

For example, it appears that you changed the contents of F3 multiple times, eventually settling on the word "Total". Once the code is recorded, you should go into the VBA editor and clean up all the extraneous instructions.

On that note, I would like to add that there is rarely a need to Select an object in VBA in order to perform an operation on it. In most cases, you can perform the operation directly on the object with a single instruction.

e.g. instead of this…

     ActiveCell.FormulaR1C1 = "=SUM(RC[-4]:RC[-1])"

You can use this:

     Range("F6").FormulaR1C1 = "=SUM(RC[-4]:RC[-1])"

The resulting code will be much more efficient.

The 24 lines of recorded code that you posted can be reduced to these 5 instructions:

Sub SumAcross_v2()
    Range("F6:F29") = "=SUM(RC[-4]:RC[-1])"
    Range("F8,F11:F19,F28:F29").NumberFormat = "$#,##0.00"
    Range("F30").FormulaR1C1 = "=(R[-1]C-R[-2]C)/ABS(R[-1]C)"
    Range("F31:F32").FormulaR1C1 = "=AVERAGE(RC[-4]:RC[-1])"
    Range("F3") = "Total"
End Sub

As for your question (if I understand it correctly) you want to insert the formulas into the next empty column in your worksheet. Therefore, instead of hardcodeing the “F”, you need VBA to determine which column to use.

This takes us back to the instruction I offered in your other thread:

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

However, we need to figure out what to use for the RowIndex argument, since I assume Row 1 isn’t the longest Row. If you know which row will always be the longest, then you can use that row number to determine the next empty column. If you don’t know which will be the longest row, then you need to determine that within the code. (Get back to me if that is an issue that needs to be addressed)

For now, let’s say you know that Row 6 will always be the longest row. If that’s the case, then you can use this method to determine the next empty column:

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

Once you have that number, your code would look like this:

Sub SumAcross_v3()
'Determine next empty column in Row 6
  nxtCol = Cells(6, Columns.Count).End(xlToLeft).Column + 1
'Insert formulas for Rows 6:29
    Range(Cells(6, nxtCol), Cells(29, nxtCol)).FormulaR1C1 = "=SUM(RC[-4]:RC[-1])"

''Build Range string for non-contiguous cell
       myRange = Cells(8, nxtCol).Address & "," & _
                 Range(Cells(11, nxtCol), Cells(19, nxtCol)).Address & "," & _
                 Range(Cells(28, nxtCol), Cells(29, nxtCol)).Address
''Set Format for non-contiguous range
          Range(myRange).NumberFormat = "$#,##0.00"
'Insert formulas, text, etc.
    Cells(30, nxtCol).FormulaR1C1 = "=(R[-1]C-R[-2]C)/ABS(R[-1]C)"
    Range(Cells(31, nxtCol), Cells(32, nxtCol)).FormulaR1C1 = "=AVERAGE(RC[-4]:RC[-1])"
    Cells(3, nxtCol) = "Total"
End Sub

Allow me explain the combination of the Range & Cells method in case you find it confusing.

As you know, you can refer to a single cell in the following manners:

Cells(6, 6)
Cells(6, "F")
Cells(RowIndex, ColumnIndex)

To refer to a range of cells, the easiest method is this:


However, there are times when we need to refer to a range of cells when we don’t want to hardcode the Range, such as in your case. In that case we use a combination of the Range and Cells methods:

Range(Cells(RowIndex_Start, ColumnIndex_Start), Cells(RowIndex_End, ColumnIndex_End))


Range(Cells(3, 5), Cells(10, 12))

Which is the same as:


That method works great for contiguous ranges with variable row and Column values, but not so well when you have non-contiguous ranges. In that case, it’s advantageous to build a text string of Range addresses as I did in the code:

      myRange = Cells(8, nxtCol).Address & "," & _
                Range(Cells(11, nxtCol), Cells(19, nxtCol)).Address & "," & _
                Range(Cells(28, nxtCol), Cells(29, nxtCol)).Address

Then, using myRange as the Range variable, it will resolve to this, assuming the nxtCol = 6 ("F")


Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03

Report •

December 16, 2014 at 13:43:06
Wow you are awesome!!!!

I got it all to work and figured out how to add borders. Do you know how I can set the print area from A3 to the last row/column?

I do believe my project will be complete, until they ask me for more enhancements that is :)

Thank you again for your time!!


Report •

December 16, 2014 at 15:43:54

Report •

Related Solutions

Ask Question