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.
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.