Summing a Range

Say you want to place a SUM function in the active cell to add the values of the cells below it, down to the next empty cell. You can do that with the following code:

With ActiveCell

Set rng = Range(.Offset(1), .Offset(1).End(xlDown)) .Formula = "=SUM(" & rng.Address & ")" End With

The Address property of the Range object returns an absolute address by default. If you want to be able to copy the formula to other cells and sum the data below them, you can change the address to a relative one and perform the copy as follows:

With ActiveCell

Set rng = Range(.Offset(1), .Offset(1).End(xlDown))

.Formula = "=SUM(" & rng.Address(RowAbsolute:=False, _

ColumnAbsolute:=

=False) & ")"

.Copy Destination:=Range(.Cells(1), .Offset(1).End(xlToRight;

1.Offset(-l))

End With

The end of the destination range is determined by dropping down a row from the SUM, finding the last data column to the right, and popping back up a row.

Figure 4-9 shows what you get after selecting B2 and running the code.

Cln d

w RangÄlfiifcrt.ütsm - Microseft E ::l

y m

Heme

InsErt

PagE Layout

Formula? Data

Review VtHl

ieveloper

■&i -

BZ

-

,u

=SUM|B3:B16}

A,

!

B :

Q

D

E

F

G !

H

I

d

1

Jail

Feb Mar Apr

May Jun

Jul

2

Total

I

14021

1455

1467

1508

1435

1592

1639

3

Prodi

13

104

70

183

97

ID

174

J

Prod2

177

91

19

72

10

126

71

5

Prod 3

177

111

127

33

145

150

164

6

Prod4

38

34

94

151

130

101

31

7

ProdS

7

ioe

126

116

157

1

61

S

ProdS

co to

167

186

2

176

190

195

9

Prod7

47

188

3E

141

77

130

193

to

Prod8

1S1

27

85

21

104

131

151

11

Prod 9

s

182

120

103

181

48

134

12

ProdlQ

166

55

155

61

21

168

134

13

Prodi 1

24

121

172

124

103

145

132

14

Prod 12

32

78

158

188

23

197

8

15

Prod13

166

53

45

131

47

191

38

16

Prod14

130

138

74

174

158

4

155

17

h

k M

RllCefe

Co lor Leib V..v~-

Safes

Consolida ODQ ''Ii'I

uum

H

Read;.

jä 3i

Bl^SiB

I,

Figure 4-9

0 0

Post a comment