I want to put the total on the last row of the sheet regardless the number of the records.

If 10 rows are populated, i want to put total on 11th row.
If 12 rows are populated, i want to put total on 13th row.

If I based the adjustment on the recordcount, its not working.

Can you explain the context a bit?

If you absolutely can not have the totals at the top,(which is where I place all my totals in excel), then you may have to do a 'For' 'Next' loop in order to find the last entry. (see Example 1)

Example1:

Dim rngCell As Range ' Create a Cell range object...
Dim tRow As Long     ' Create a variable for the totals row...
' "For" loop thru all cells in your column of choice...
For each rngCell in Sheet("Your Sheet").Range("A:A")
    ' Keep going till you hit a blank...
    If rngCell = "" then
        ' Save the row number...
        tRow = rngCell.Row
        ' Exit the "For" loop...
        Exit For
    End If
Next rngCell
' Enter your totals' formulas on the found last row...
Sheets("Your Sheet").Cells(tRow,1) = 'Your Formula...

Although, if you are using VBA to add your entries to begin with, you could always just insert a new row and add your entry on that new row, then your formulas would just automatically shift it's range provided you always insert within your range. (see Example 2)

Example2:

     |     A     |     B     |     C     |     D     |
------------------------------------------------------
  1  | Heading 1 | Heading 2 | Heading 3 | Heading 4 |
------------------------------------------------------
  2  |   100     |    2      |      52   |    ...    |
------------------------------------------------------
  3  |   200     |    5      |      34   |    ...    |
------------------------------------------------------
  4  |    20     |    2      |     ...   |    ...    |
------------------------------------------------------ <--- Insert New row here...
  5  |           |           |           |           | <--- Leave this row blank,
------------------------------------------------------      but include it in your formula
  6  | =Sum($A$2:$A$5) |...  |     ...   |    ...    | <--- Your formula will auto-increment.
------------------------------------------------------

How are you triggering when your totals are to be added?
How / why are your data entries changing or different?
Let us know a bit more... give us a scenario.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.