PDA

View Full Version : Find Blank Row and Sum Cells Above it??



Hankins
03-18-2007, 06:57 PM
I have a column of data that I have a few blank rows seperating values. I want to find the first blank row and sum the data above it. For example:

400
300
355
200
Blank << Sum Here of (400,300,355,200)
Blank
Blank
300
200
100
150
Blank << Sum Here of (300,200,100,150)
Blank
Blank


Please Help.:help

geekgirlau
03-18-2007, 08:18 PM
This assumes that the data is in column 1, but could be easily modified for another column.

Sub TotalSections()
Dim rng As Range
Dim lngRowStart As Long


' mark the start of the first group (may not be row 1)
If Range("A1").Formula <> "" Then
lngRowStart = 1
Else
lngRowStart = Range("A1").End(xlDown).Row
End If

Do
' find the end of the group
Set rng = Cells(lngRowStart, 1).End(xlDown).Offset(1, 0)

' make sure it hasn't already been totalled
If Left(rng.Offset(-1, 0).Formula, 1) <> "=" Then
' sum from start of group to last cell
rng.FormulaR1C1 = "=sum(R[-" & rng.Row - lngRowStart & "]C:R[-1]C)"
End If

' locate next group
lngRowStart = rng.End(xlDown).Row

' after the last group, test for end of sheet
If lngRowStart = 65536 Then Exit Sub
Loop
End Sub

lucas
03-18-2007, 09:36 PM
Nice looping code Anne. I didn't try it on large amounts of data but it works great on a small sample.

geekgirlau
03-18-2007, 10:11 PM
It's probably adequate for a small sample, but would require a rethink for larger sets of data :thinking:

mdmackillop
03-19-2007, 01:32 AM
I'd suggest
If lngRowStart = Cells.Rows.Count Then Exit Sub
to cater for Excel 2007

geekgirlau
03-19-2007, 05:31 PM
Good point - 'spose we need to start catering for that ...