# summing selections

#### nova30

##### New Member
Hi,

Is there a way to sum a range of cell, without actually reffering to the cel refferences. I am currently writing a macro which formats a variable length worksheet. Consequently I am having to use words in cells to define my selection, rather than the cell refferences. I need to find a way of summing the values between a cell named "Household" and a cell named "Subtotal". Both values are stored in column C, and the number of rows between these field changes so I can not hard wire in the actual formula.

Can anyone help!!

Cheers

Nova30

### 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"
Hi nova30,

<pre>
'sum between names
Sub sumrange()
Dim SumArea As Range, Answer As Long

Set SumArea = Sheets("Sheet2").Range("Household", "Subtotal")

End Sub

</pre>

HTH

Hi Richie

Thanks for the help, but I dont think i made my self very clear as the calculation is a little bit more complicated than I originally suggested!! Here is the code that I have written:

Public Sub SelectTabel()
Dim cStart As Range
Dim cEnd As Range
Dim cStartcal As Range
Dim cEndCal As Range

' first look for the first cell with *post store opening* in it
Set cStart = Range("A1")
' now check for the number cell
While Not LCase(Mid(cStart.Value, 1, 18)) = "post store opening"
' set the cEnd to the next cell
Set cStart = cStart.Offset(1, 0)
Wend
' set the end equal to the start range
Set cEnd = cStart
' Offset the cStart cell to the first cell in range for summing
Set cStartcal = cStart.Offset(2, 3)
' now check for the total cell
While Not LCase(cEnd.Value) = "total"
' set the cEnd to the next cell
Set cEnd = cEnd.Offset(1, 0)
Wend
' Offset the cStart cell to the last cell in range for summing
Set cEndCal = cEnd.Offset(0, 2)
' now select the range you need
' This is the selection that I need to sum
Range("C" & cStartcal.Row & ":" & "C" & cEndCal.Row).Select
End Sub

All I really need to do is find a way of summing the range in the last couple of lines of code. This range selects all the required infomation and allows the table to vary in length.

Is this possible? and how would I sum the selection?

Cheers,

Nova30

Replies
13
Views
567
Replies
1
Views
105
Replies
3
Views
414
Replies
1
Views
249
Replies
12
Views
581

1,218,560
Messages
6,143,206
Members
450,469
Latest member
brent3162

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