Excel macro - Find very last row on sheet, then add a calculation to column L

I have an Excel sheet that the number of rows are constantly changing.  

I currently have a macro that adds 10 new rows to the bottom of the sheet and highlights the ten new rows (thank you dave4dl):

Dim curcell As Range
Dim highlightRange As Range
Set curcell = Range("a1").SpecialCells(xlLastCell)
Set highlightRange = Range(Cells(curcell.Row +1,1), Cells(curcell.Row +10,12))
highlightRange.Interior.Color = RGB(255,255,0)

I am now looking for a macro that will add a calculation to column L,  rows 5 - ??.  The calculation is the sum of columns G and I (=SUM(G#:I#) for each row.  This calculation is to be added to column L, rows 5 - ??(to the end of the 10 new highlighted rows).
Who is Participating?
Hi nulad,

Using SpecialCells like that can give errors if the sheet is blank, personally I prefer using Find and searching backwards by rows, helps avoid the 'fake' last cells.
To see what I mean about the 'fake' last cells, create a new sheet, put "hi" in cell B2, then go cell J10 and make the cell bold (there are many things that can trigger fake last cells, formatting is just one of them).  The last used cell should be B2, but if you press control-end to go to the last cell, you'll go to J10.  Using the .Find method finds the last cell with data.  Give the following a try, should do exactly as you need:

Sub nulad()
 Dim curcell As Range
 Set curcell = Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
 If curcell Is Nothing Then Exit Sub 'sheet is blank
 Application.ScreenUpdating = False
 curcell.Offset(1, 0).EntireRow.Cells(1).Resize(10, 12).Interior.Color = RGB(255, 255, 0)
 If curcell.Row >= 5 Then Range("L5", Cells(curcell.Row + 10, 12)).FormulaR1C1 = _
 Application.ScreenUpdating = True
End Sub

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.