Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
summing up cells up to the first unused cell encountered
Hi,
What would be the VBA code to sum up cells up to the first unused cell encountered? For example, if cells A10-A12 have numbers, I want cell A13 to sum from cell A12 up to the first unused cell (in this case A9). I cannot just sum up the entire column A because there is data in cells A1-A8 that I don't want to sum. Thanks very much! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
summing up cells up to the first unused cell encountered
I suppose this would work, there will be problems though if the next
time you run the code the the last sum wasn't cleared so this code will clear it first. Dim s As Range Set s = Range("A65536").End(xlUp) s.ClearContents 'clears the last entry in Column A Range("A9").End(xlDown).Offset(2, 0) = _ Application.Sum(Range(Range("A9"), Range("A9").End(xlDown))) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
summing up cells up to the first unused cell encountered
Thanks CurlyDave! Unfortunately, everytime I run the data, the data
group range I want to sum would start in different rows (depending on how many rows the data group above it has), so is there any way to tell the code to search bottom-up? Thanks again. On Feb 24, 8:58*pm, CurlyDave wrote: I suppose this would work, *there will be problems though if the next time you run the code the the last sum wasn't cleared so this code will clear it first. Dim s As Range Set s = Range("A65536").End(xlUp) s.ClearContents 'clears the last entry in Column A Range("A9").End(xlDown).Offset(2, 0) = _ Application.Sum(Range(Range("A9"), Range("A9").End(xlDown))) |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
summing up cells up to the first unused cell encountered
Try This
Dim a As Range, s As Range, r As Range Set a = Range("A1").End(xlDown).Offset(1, 0) Set s = Range("A65536").End(xlUp) s.ClearContents 'clears the last entry in Column A should be the previous formula Set r = Range("A65536").End(xlUp) r.Offset(2, 0) = _ Application.Sum(Range(a, r)) |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
summing up cells up to the first unused cell encountered
Thanks so much Dave!
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
what to do with unused cells | Excel Worksheet Functions | |||
deleting unused cells / getting rid of inactive cells | Excel Discussion (Misc queries) | |||
Is there a way to cut off unused cells on a sheet | Excel Discussion (Misc queries) | |||
how get rid of cells with unused formulas | Excel Discussion (Misc queries) | |||
Help with getting rid of unused cells or ranges | Excel Programming |