Solved: How will I modify a VBA code that will only calculate the last populated row?

[genracela]

I created a VBA code for cell formulas:

Sub calc2()
Range("AA8:AA550").Formula = "=IFERROR((SUMIF(U8:Y8,"">0"")),0)"
Range("AB8:AB550").Formula = "=IFERROR((SUMIF(U8:Y8,"">0"")/SUMIF(M8:P8,"">0"")),0)"
Range("AC8:AC550").Formula = "=IFERROR((SUMIF(U8:Y8,"">0"")+AM8-SUM(M8:M8)),0)"
Range("AD8:AD550").Formula = "=IFERROR(((SUMIF(U8:Y8,"">0"")+AM8)/SUMIF(M8:P8,"">0"")),0)"
Range("AE8:AE550").Formula = "=IFERROR(((SUMIF(U8:Y8,"">0"")+AM8)/SUMIF(M8:T8,"">0"")),0)"
Range("AF8:AF550").Formula = "=IFERROR((SUMIF(U8:Y8,"">0"")-SUMIF(M8:T8,"">0"")),0)"
Range("AG8:AG550").Formula = "=IFERROR((SUMIF(U8:Y8,"">0"")-SUMIF(M8:M8,"">0"")),0)"
Range("AH8:AH550").Formula = "=IFERROR((IF(VLOOKUP(A8,Category!A:B,2,0)=1,1,IF(L8=0,1.4,IF(L8<=L$6,1,1.4)))),0)"
Range("AI8:AI550").Formula = "=IFERROR((IF(L8=0,IF(SUM(U8:Y8)+AM8-SUM(M8:T8)>0,0,SUM(U8:Y8)+AM8-SUM(M8:T8)),IF(L8<=AI$6,0,IF(SUM(U8:Y8)+AM8-SUM(M8:T8)>0,0,SUM(U8:Y8)+AM8-SUM(M8:T8))))),0)"
Range("AJ8:AJ550").Formula = "=IFERROR((ROUND(F8*AI8,0)),)"
Range("AK8:AK550").Formula = "=IFERROR((U8/(N8+O8+P8)),0)"
Range("AL8:AL550").Formula = "=IFERROR((Y8/(N8+O8+P8)),0)"
Range("AM8:AM550").Formula = "=IFERROR((IF(AND($CW8<=$DB8,$DD8<100%),$CU8,0)),0)"
Range("AN8:AN550").Formula = "=IFERROR((IF(AND($CW8>$DB8,$CW8<$DC8,$DD8<100%),$CU8,0)),0)"
Range("AO8:AO550").Formula = "=IFERROR((IF(AND($CW8>$DC8+14,$CW8<$DC8+60,$DD8<100%),$CU8,0)),0)"
Range("AP8:AP550").Formula = "=IFERROR((IF(AND($CW8>=$DC8+60,$DD8<100%),$CU8,0)),0)"
End Sub


My problem is, formulas are populated in the cells until row 550 on every column even if there are no data to calculate, and it shows "0" in every cell.

Question:
How will I modify this code when I only want to calculate the rows that has data?

How do "last row" code works?:help

Thanks in advance!

[mbarron]

Change the Range("A:A") to a column containing data in the last row. Then use the variable lRow in your Range().Formula


Sub Cals2()
Dim lRow As Long
'find last row with non blank value in column A
lRow = Range("A:A").Find(what:="*", LookIn:=xlValues, SearchOrder:=xlByRows, _
searchdirection:=xlPrevious).Row

'use last row variable in Range()
Range("AA8:AA" & lRow).Formula = "=IFERROR((SUMIF(U8:Y8,"">0"")),0)"
' the rest of your code

[genracela]

Thanks! It's perfect!

Follow-up question:

When I run the calculation, the formula in my VBA code shows in the formula bar. Is there a way to just populate the cells with just answers? without showing the formula in the formula bar?

If yes, how?

Thanks a million in advance!!!

[mbarron]

I would do a Copy / Paste Special - Values


Range("AA8:AP" & lRow).Copy
Range("AA8").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Range("AA8").Select 'can be omitted

[genracela]

wow! this is cool!

Thanks! Thanks! Thanks! :clap: :hug: :dance: