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

Posted on 2006-04-05
Last Modified: 2012-08-14
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).
Question by:nulad
    1 Comment
    LVL 35

    Accepted Solution

    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


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Suggested Solutions

    Title # Comments Views Activity
    bigDiff challenge 17 57
    method argument as final 1 54
    allStar challenge 1 27
    wordmultiple challenge 12 63
    This article will show, step by step, how to integrate R code into a R Sweave document
    If you’re thinking to yourself “That description sounds a lot like two people doing the work that one could accomplish,” you’re not alone.
    In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now