Sub AddBlankLines()
Dim rng As Range, rng1 As Range
Dim col As Long
Set rng1 = Range("A2")
Set rng = Range("A3")
col = Cells(3, 256).End(xlToLeft).Column - 1
Do
If rng.Value < rng.Offset(-1, 0).Value Then
rng.EntireRow.Insert
Cells(rng.Row - 1, 2).Resize(1, col).Formula = _
"=Subtotal(9," & Range(rng1.Offset(0, 1), _
Cells(rng.Row - 2, 2)).Address(0, 0) & ")"
Set rng1 = rng
rng.Offset(-1, 0).Value = "Total " & _
rng.Offset(-2, 0).Value
rng.Offset(-1, 0).Font.Bold = True
End If
Set rng = rng.Offset(1, 0)
Loop Until IsEmpty(rng)
Set rng = rng.Offset(1, 0)
Cells(rng.Row - 1, 2).Resize(1, col).Formula = _
"=Subtotal(9," & Range(rng1.Offset(0, 1), _
Cells(rng.Row - 2, 2)).Address(0, 0) & ")"
rng.Offset(-1, 0).Value = "Total " & _
rng.Offset(-2, 0).Value
rng.Offset(-1, 0).Font.Bold = True

End Sub


--
Regards,
Tom Ogilvy

projectside wrote in message
...
yes i could have used that. but the thing is, the number of columns to
be summed varries from time to time.



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/