We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x

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

on
Medium Priority
3,001 Views
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).
Comment
Watch Question

## View Solution Only

CERTIFIED EXPERT
Commented:

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:

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 = _
"=SUM(RC[-5]:RC[-3])"
Application.ScreenUpdating = True
End Sub

Matt

Not the solution you were looking for? Getting a personalized solution is easy.

##### Thanks for using Experts Exchange.

• View three pieces of content (articles, solutions, posts, and videos)
• Ask the experts questions (counted toward content limit)
• Customize your dashboard and profile