Sum of dynamic range something wrong.

[Narendra Vashisth]

Merchant Name	Vehicle No.	Txn Date	Txn Type	Amount
HDFC CMS	-	01-03-2023 7:58​	Recharge	200000​
HDFC CMS	-	01-03-2023 13:18​	Recharge	200000​
HDFC CMS	-	01-03-2023 19:18​	Recharge	100000​
HDFC CMS	-	02-03-2023 8:58​	Recharge	200000​
HDFC CMS	-	02-03-2023 19:03​	Recharge	200000​
HDFC CMS	-	03-03-2023 13:18​	Recharge	200000​
HDFC CMS	-	03-03-2023 19:33​	Recharge	300000​
HDFC CMS	-	04-03-2023 19:08​	Recharge	300000​
HDFC CMS	-	05-03-2023 22:13​	Recharge	200000​
HDFC CMS	-	06-03-2023 19:38​	Recharge	300000​
HDFC CMS	-	07-03-2023 8:28​	Recharge	300000​
HDFC CMS	-	07-03-2023 17:53​	Recharge	300000​
HDFC CMS	-	08-03-2023 17:28​	Recharge	200000​
HDFC CMS	-	09-03-2023 17:03​	Recharge	200000​
HDFC CMS	-	09-03-2023 19:28​	Recharge	400000​
HDFC CMS	-	10-03-2023 15:48​	Recharge	300000​
HDFC CMS	-	10-03-2023 21:23​	Recharge	300000​
HDFC CMS	-	11-03-2023 19:08​	Recharge	500000​
HDFC CMS	-	12-03-2023 16:23​	Recharge	300000​
HDFC CMS	-	13-03-2023 16:43​	Recharge	200000​
HDFC CMS	-	13-03-2023 19:18​	Recharge	200000​
HDFC CMS	-	14-03-2023 12:09​	Recharge	300000​
HDFC CMS	-	14-03-2023 19:02​	Recharge	250000​
HDFC CMS	-	15-03-2023 15:28​	Recharge	300000​
HDFC CMS	-	15-03-2023 20:18​	Recharge	300000​
HDFC CMS	-	16-03-2023 19:25​	Recharge	300000​
HDFC CMS	-	17-03-2023 17:05​	Recharge	200000​
HDFC CMS	-	17-03-2023 18:20​	Recharge	10​
HDFC CMS	-	17-03-2023 19:23​	Recharge	200000​
HDFC CMS	-	18-03-2023 12:34​	Recharge	300000​
HDFC CMS	-	18-03-2023 13:32​	Recharge	850000​
HDFC CMS	-	18-03-2023 18:43​	Recharge	300000​
HDFC CMS	-	19-03-2023 17:38​	Recharge	200000​
HDFC CMS	-	20-03-2023 11:44​	Recharge	200000​
HDFC CMS	-	20-03-2023 18:56​	Recharge	300000​
HDFC CMS	-	21-03-2023 14:08​	Recharge	300000​
HDFC CMS	-	21-03-2023 19:00​	Recharge	200000​
HDFC CMS	-	22-03-2023 14:55​	Recharge	200000​
HDFC CMS	-	22-03-2023 19:20​	Recharge	300000​
HDFC CMS	-	23-03-2023 20:23​	Recharge	300000​
HDFC CMS	-	24-03-2023 12:45​	Recharge	300000​
HDFC CMS	-	24-03-2023 19:09​	Recharge	300000​
HDFC CMS	-	25-03-2023 13:03​	Recharge	200000​
HDFC CMS	-	25-03-2023 19:25​	Recharge	400000​
HDFC CMS	-	26-03-2023 13:59​	Recharge	300000​
HDFC CMS	-	27-03-2023 16:06​	Recharge	200000​
HDFC CMS	-	27-03-2023 19:16​	Recharge	300000​
HDFC CMS	-	28-03-2023 14:21​	Recharge	200000​
HDFC CMS	-	28-03-2023 19:43​	Recharge	300000​
HDFC CMS	-	29-03-2023 15:20​	Recharge	200000​
HDFC CMS	-	29-03-2023 20:14​	Recharge	300000​
HDFC CMS	-	30-03-2023 14:26​	Recharge	200000​
HDFC CMS	-	30-03-2023 19:18​	Recharge	200000​
HDFC CMS	-	31-03-2023 19:58​	Recharge	300000​
Highway Petronet	-	18-03-2023 13:35​	CCMS Sale Completion	50000​
Highway Petronet	-	18-03-2023 13:36​	CCMS Sale Completion	800000​
Highway Petronet	-	22-03-2023 14:59​	CCMS Sale Completion	1000000​
Highway Petronet	-	24-03-2023 14:03​	CCMS Sale Completion	122663​
Highway Petronet	-	26-03-2023 9:28​	CCMS Sale Completion	495110​
JAMNAGAR FILLING STATION	-	24-03-2023 18:01​	CCMS Sale Completion	1211874.74​
					1706985​
TAJ MIDWAY	-	20-03-2023 18:44​	CCMS Sale Completion	100912.51​
TAJ MIDWAY	-	22-03-2023 15:40​	CCMS Sale Completion	1089672.76​
TAJ MIDWAY	-	22-03-2023 18:39​	CCMS Sale Completion	180572.35​
TAJ MIDWAY	-	23-03-2023 7:46​	CCMS Sale Completion	291489.23​
TAJ MIDWAY	-	23-03-2023 10:12​	CCMS Sale Completion	183312.8​
TAJ MIDWAY	-	23-03-2023 14:44​	CCMS Sale Completion	45210​
TAJ MIDWAY	-	23-03-2023 17:06​	CCMS Sale Completion	44140.4​
TAJ MIDWAY	-	23-03-2023 17:57​	CCMS Sale Completion	40000​
TAJ MIDWAY	-	24-03-2023 7:58​	CCMS Sale Completion	112662.41​
TAJ MIDWAY	-	24-03-2023 13:30​	CCMS Sale Completion	94767.2​
TAJ MIDWAY	-	24-03-2023 15:34​	CCMS Sale Completion	76414.85​
TAJ MIDWAY	-	24-03-2023 18:52​	CCMS Sale Completion	24413.4​
TAJ MIDWAY	-	24-03-2023 20:53​	CCMS Sale Completion	36168​
TAJ MIDWAY	-	25-03-2023 10:40​	CCMS Sale Completion	2712.6​
TAJ MIDWAY	-	25-03-2023 18:05​	CCMS Sale Completion	394.49​
TAJ MIDWAY	-	28-03-2023 14:08​	CCMS Sale Completion	620961.14​
TAJ MIDWAY	-	29-03-2023 9:32​	CCMS Sale Completion	431392.73​
TAJ MIDWAY	-	29-03-2023 17:20​	CCMS Sale Completion	314766.48​
TAJ MIDWAY	-	29-03-2023 18:51​	CCMS Sale Completion	122634.83​
TAJ MIDWAY	-	30-03-2023 9:58​	CCMS Sale Completion	265097.42​
TAJ MIDWAY	-	30-03-2023 14:41​	CCMS Sale Completion	419103.38​
TAJ MIDWAY	-	30-03-2023 16:04​	CCMS Sale Completion	36168​
TAJ MIDWAY	-	31-03-2023 8:30​	CCMS Sale Completion	263878.78​
TAJ MIDWAY	-	31-03-2023 20:40​	CCMS Sale Completion	126350.82​
TAJ MIDWAY	-	31-03-2023 21:14​	CCMS Sale Completion	2461.42​
					4925658​

 

[Narendra Vashisth]

Dim y As Variant
Dim firstRow As Variant
lastRow = Range("E" & Rows.Count).End(xlUp).Row
firstRow = Cells(lastRow, 5).End(xlUp).Row ' Five shows row no for adding total

If IsNumeric(Cells(lastRow + 1, 1)) And IsEmpty(Cells(lastRow + 1, 2)) Then
Cells(lastRow + 1, 6).Formula = "=SUM(E" & firstRow & ":E" & lastRow & ")"
Cells(lastRow + 1, 6).Select
Selection.Font.Bold = True
End If


For y = firstRow To 2 Step -1
lastRow = Cells(y, 5).End(xlUp).Row
firstRow = Cells(lastRow, 5).End(xlUp).Row


If IsNumeric(Cells(lastRow + 1, 1)) And IsEmpty(Cells(lastRow + 1, 2)) Then
Cells(lastRow + 1, 6).Formula = "=SUM(E" & firstRow & ":E" & lastRow & ")"

Cells(lastRow + 1, 6).Select
Selection.Font.Bold = True
End If
y = firstRow
Next y

sir, i am new in this form & i am big fan of your work.
as you see in single value sum it taking wrong selection & after that exit, kindly plz solve the problem

 

[Alex Blakenburg]

I tried to stay close to what you already had but give this a try.

Sub AddSubtotals()

    Dim firstRow As Long
    Dim lastRow As Long

    lastRow = Range("E" & Rows.Count).End(xlUp).Row
    
    Do Until lastRow = 1                                ' Stop when you get to the heading row
        
        If Cells(lastRow - 1, 5) = "" Then              ' Check for sectioin with only one value
            firstRow = lastRow
        Else
            firstRow = Cells(lastRow, 5).End(xlUp).Row  ' Five shows row no for adding total
        End If
        
        If IsNumeric(Cells(lastRow + 1, 1)) And IsEmpty(Cells(lastRow + 1, 2)) Then
            Cells(lastRow + 1, 6).Formula = "=SUM(E" & firstRow & ":E" & lastRow & ")"
            Cells(lastRow + 1, 6).Select
            Selection.Font.Bold = True
        End If
        
        If firstRow = 1 Then firstRow = 2               ' Exclude the heading row
       
        If IsNumeric(Cells(lastRow + 1, 1)) And IsEmpty(Cells(lastRow + 1, 2)) Then
            Cells(lastRow + 1, 6).Formula = "=SUM(E" & firstRow & ":E" & lastRow & ")"
            
            Cells(lastRow + 1, 6).Select
            Selection.Font.Bold = True
        End If
        
        lastRow = Range("E" & firstRow).End(xlUp).Row   ' Find bottom of next section
    Loop

End Sub