Dear All,

I have an excel sheet which will be filled with data from
database dynamically, e.g.

Field_A Field_B

A 1
B 2
C 3
D 4

Now after the data has been filled, I want Excel to add a
Total sum following the last record.
just like following:

Field_A Field_B
A 1
B 2
C 3
D 4
Total 10

How can I do that in VBA?

Thanks.

LastRow = Cells(Rows.Count,"B").End(xlUp).Row
Cells(LastRow,"B").Formula = "=SUM(B1:B" & LastRow & ")"

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"hon123456" wrote in message
ps.com...
Dear All,

I have an excel sheet which will be filled with data from
database dynamically, e.g.

Field_A Field_B

A 1
B 2
C 3
D 4

Now after the data has been filled, I want Excel to add a
Total sum following the last record.
just like following:

Field_A Field_B
A 1
B 2
C 3
D 4
Total 10

How can I do that in VBA?

Thanks.

I think that Bob will be writing over the last value in column B. Maybe...

Dim LastRow as Long
with activesheet
lastrow = .cells(.rows.count,"A").end(xlup).row
.cells(lastrow+1,"A").value = "Total"
.cells(lastrow+1,"B").formula = "=sum(b1:b" & lastrow & ")"
end with




Bob Phillips wrote:

LastRow = Cells(Rows.Count,"B").End(xlUp).Row
Cells(LastRow,"B").Formula = "=SUM(B1:B" & LastRow & ")"

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"hon123456" wrote in message
ps.com...
Dear All,

I have an excel sheet which will be filled with data from
database dynamically, e.g.

Field_A Field_B

A 1
B 2
C 3
D 4

Now after the data has been filled, I want Excel to add a
Total sum following the last record.
just like following:

Field_A Field_B
A 1
B 2
C 3
D 4
Total 10

How can I do that in VBA?

Thanks.


--

Dave Peterson

Thanks for you all,

My another question is how can I sum the result
horizontally?

Thanks

LastCol = Cells(2, Columns.Count).End(xlToLeft).Column
Cells(2, LastCol + 1).FormulaR1C1 = "=SUM(R2C1:R2C" & LastCol & ")"


(and I remembered to offset the cell this time <g)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"hon123456" wrote in message
ps.com...
Thanks for you all,

My another question is how can I sum the result
horizontally?

Thanks