# Dynamic Cells for Sum Function

##### New Member
Hi all,

Hope You are doing fine in this pandemic season.

So I try to make macro using vba where you can add after the data is input to the sheet

here's what it's look like when the data is input the first time

the button corresponding to the addition some of the cells. If I push the buttons, here's what it looks like

It works fine, so far so good.

but then the problem comes if i have multiple input like this

so after i sum the second time the total getting mixed all the way to the top.
How to set the code so that the sum will start right after this "--------" cell?
Here's the VBA code for this:

VBA Code:
``````Sub Total()

Dim LastRow As Long, LastCol As Long, Total As Long
Dim StartRow As Long
Dim StartRange As Range
Dim workingSheet As Worksheet
Set workingSheet = ThisWorkbook.Sheets("Sheet1")

With workingSheet

LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column

StartRow = .Cells(.Cells(.Rows.Count, 1).End(xlUp).Row).End(xlUp).Row

.Cells(LastRow + 1, 5).Value = "Total: "
.Cells(LastRow + 1, 6).Value = "=SUM(F" & StartRow + 2 & ":F" & LastRow & ")"
.Cells(LastRow + 1, 7).Value = "=SUM(G" & StartRow + 2 & ":G" & LastRow & ")"
.Cells(LastRow + 1, 8).Value = "=SUM(H" & StartRow + 2 & ":H" & LastRow & ")"
.Cells(LastRow + 1, 10).Value = "=SUM(J" & StartRow + 2 & ":J" & LastRow & ")"
.Cells(LastRow + 1, 11).Value = "=SUM(K" & StartRow + 2 & ":K" & LastRow & ")"
.Cells(LastRow + 1, 12).Value = "=SUM(L" & StartRow + 2 & ":L" & LastRow & ")"
.Cells(LastRow + 1, 13).Value = "=SUM(M" & StartRow + 2 & ":M" & LastRow & ")"

.Cells(LastRow + 1, 1).Value = "---------"
.Cells(LastRow + 1, 2).Value = "---------"
.Cells(LastRow + 1, 3).Value = "---------"
.Cells(LastRow + 1, 4).Value = "---------"
.Cells(LastRow + 1, 9).Value = "---------"

.Cells(LastRow + 2, 1).Value = "---------"
.Cells(LastRow + 2, 2).Value = "---------"
.Cells(LastRow + 2, 3).Value = "---------"
.Cells(LastRow + 2, 4).Value = "---------"
.Cells(LastRow + 2, 5).Value = "---------"
.Cells(LastRow + 2, 6).Value = "---------"
.Cells(LastRow + 2, 7).Value = "---------"
.Cells(LastRow + 2, 8).Value = "---------"
.Cells(LastRow + 2, 9).Value = "---------"
.Cells(LastRow + 2, 10).Value = "---------"
.Cells(LastRow + 2, 11).Value = "---------"
.Cells(LastRow + 2, 12).Value = "---------"
.Cells(LastRow + 2, 13).Value = "---------"

End With

End Sub``````

Sorry for the long post

### Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

#### Eric W

##### MrExcel MVP
Give this a shot:

VBA Code:
``````Sub Total()
Dim lr As Long, ld As Long

lr = Cells(Rows.Count, "A").End(xlUp).Row
ld = Evaluate("AGGREGATE(14,6,ROW(A1:A" & lr & ")/(LEFT(A1:A" & lr & ")=""-""),1)")

Range(Cells(lr + 1, "A"), Cells(lr + 2, "M")).Value = "---------"
Cells(lr + 1, "E").Value = "Total:"
Range(Cells(lr + 1, "F"), Cells(lr + 1, "M")).Formula = "=SUM(F" & ld + 1 & ":F" & lr & ")"
Cells(lr + 1, "I") = "---------"

End Sub``````

##### New Member
Hi Erik,

Thank you for your fast response, and I'm sorry for the late reply.

It's perfect, thank you for your help.

it's been a crazy week to find the right way and function.

#### Eric W

##### MrExcel MVP
Glad it works for you! Thanks for the feedback.

Replies
16
Views
221
Replies
10
Views
244
Replies
18
Views
239
Replies
3
Views
71
Replies
5
Views
79

1,140,926
Messages
5,703,211
Members
421,282
Latest member
hogie

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back