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

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

CERTIFIED EXPERT
Commented:
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

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

Ask the Experts
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

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

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.