This will work, assuming the data is in Column A. It does the
following:
1. It looks for the first cell in Column A that is NOT empty.
2. It then looks for the next empty cell in Column A
3. It then sums the last 20 cells of Col A and puts the result in
Cell B1
Sub Last20()
i = 0
FindStart:
i = i + 1
If Cells(i, 1) <> "" Then GoTo FindEnd
GoTo FindStart
FindEnd:
i = i + 1
If Cells(i, 1) = "" Then lrow = i - 1 Else GoTo FindEnd
' Now get sum
rowsum = 0
For i = lrow - 19 To lrow
rowsum = rowsum + Cells(i, 1).Value
Next i
' put sum in cell B1
Range("B1").Value = rowsum
End Sub
If the data to be summed is not in Column A, then in each instance
of "Cells(i,1)", replace the "1" with the appropriate column index.
pynasocas's suggested code is much more elegant. I don't
understand how to access the Debug.Print statement, but
putting "ans" onto the sheet worked fine.
In the event you have less than 20 rows of cells to sum, you will
get an error unless you substitute my:
"For i = lrow - 19 To lrow"
with:
"For i = Application.WorksheetFunction.Max(1, lrow - 19) To lrow"
A similar change would have to be made in pynasocas's code.