I think you all know about the "Sum" function in excel sheets. If you want to get the sum of values of cells from C5 to C246, you can simply write the formula "=Sum(C5:C246)" at the cell where you want the sum value to be appear.
You can use this function in VBA as well. Then you can get "Sum" value very easily.
Below is the code for that
Range("C247").Value = Application.Sum(Range("C5:C246"))

Sum value will appear at cell "C247"
Some times there are situations you need to use column numbers instead of column names when developing programs. You can use below code at those circumstances.
Range("C247").Value = Application.Sum(Range(Cells(5, 3), Cells(246, 3))) 
please note that above two codes do the same thing. So they gives same values.