# sum by rows across columns

#### jeansb

##### New Member
Can anyone help me with my code as follow:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Sheets("sheet1").Activate
Dim de As Range
Dim i, j As Integer
For Each de In Range("e1:e1000")
For i = 1 To 1000
For j = 1 To 4
de = Application.WorksheetFunction.Sum(Range(Cells(1, j), Cells(i, 1)))
Next j
Next i
Next de
End Sub

I have 5 columns, the fifth column is the total column and 1000 rows
this code does not give the correct total results in column (e1:e1000)
I think there are quite few solutions for this and it is very easy, but i can't find the correct answer.

### Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

#### Tom Urtis

##### MrExcel MVP
Well, a few things...

If this code is going into the Sheet1 module, there is no need to include an Activate line.

I cannot understand why you'd want to recalculate 1000 formulas with the mere selection of any and every cell, especially when you only need to enter the formulas once and leave them there, even if by code, instead of the static value approach you took for the WorksheetFunction Sum.

However, the short answer that comes closest to what you have, and what you are looking for, might be this, but it's an inefficient design premise, if indeed you are summing A:D for rows 1 to 1000 on every cell selection:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With Application
.ScreenUpdating = False
.EnableEvents = False
With Range("E1:E1000")
.Formula = "=SUM(RC1:RC4)"
.Value = .Value
End With
.EnableEvents = True
.ScreenUpdating = True
End With
End Sub

#### jindon

##### MrExcel MVP
Hi,

not sure if i understand what you want to do
if you have col.E to total it's row, like
=SUM(A1:E1) then

For Each de in Range("e1:e1000")
de.FormuaR1C1="=sum(rc1:rc[-1])"
next

Or

Range("e1").FormulaR1C1="=sum(rc1:rc[-1])"
Range("e1").AutoFill Destination:=Range("e1:e1000")

hope this helps
jindon

#### jeansb

##### New Member
your solution is work great !!

#### jeansb

##### New Member
How do I add a condition if sum=0 then it will show "" in total colum e ?

#### jindon

##### MrExcel MVP
Hi

Range("e1").FormulaR1C1 = "=if(sum(rc1:rc[-1])=0,"""",sum(rc1:rc[-1]))"

rgds,

jindon

Replies
4
Views
195
Replies
5
Views
766
Replies
2
Views
413
Replies
9
Views
407
Replies
5
Views
472

1,147,508
Messages
5,741,572
Members
423,668
Latest member
Audorin

### 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