• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2971
  • Last Modified:

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

Matt
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now