Look down column and enter blank row and color grey

[Les]

Hi all,

I have a sheet that is sorted by name in column "E". I need to go down this
column from row 11 till i get the next name, insert a blank row and color it
grey. then i need to total up the columns G, I, K, M, O & Q and insert the
totals in the inserted grey blank row at the bottom of the relevant columns.

Result : Sections Giving me the totals in each grey row for each person in
entire sheet.


Any help would be greatly appreciated...

 

[Mark Ivey]

Question:

Does Column E have the same name(s) repeated more than once?

Mark Ivey

 

[Les]

Hi Mark, yes it does and they are sorted together.

So the idea is to get the totals of one persons responsibilities.

 

[Dave Peterson]

Maybe you can change your requirements.

Since your data is sorted, you could select the range
data|subtotals (xl2003 menus)
At each change in column E (or the header used for column E)
Use function: Sum
And check the fields you want.

You'll be able to see the totals by clicking on the outlining symbols to the
left.

=====
Another possibility would be to use data|pivottable.

You can create a nice summary report pretty quickly.

If you've never used pivottables, here are a few links:

Debra Dalgleish's pictures at Jon Peltier's site:
http://peltiertech.com/Excel/Pivots/pivottables.htm
And Debra's own site:
http://www.contextures.com/xlPivot01.html

John Walkenbach also has some at:
http://j-walk.com/ss/excel/files/general.htm
(look for Tony Gwynn's Hit Database)

Chip Pearson keeps Harald Staff's notes at:
http://www.cpearson.com/excel/pivots.htm

MS has some at (xl2000 and xl2002):
http://office.microsoft.com/downloads/2000/XCrtPiv.aspx
http://office.microsoft.com/assistance/2002/articles/xlconPT101.aspx

 

[Mark Ivey]

Sorry so long... got a b-day party going on over here for my son. He is
turning 2 today... wahoo!

See if this code fits the situation...

Mark Ivey

'*********Code starts here

Sub totalByName()
Dim LastRowColE As Long
Dim i As Long
Dim myRow As Long

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

LastRowColE = Range("E11").End(xlDown).Row
myRow = 11
i = 11

While i <> LastRowColE
If Cells(myRow, 5).Value <> Cells(myRow + 1, 5).Value Then
Cells(myRow + 1, 5).EntireRow.Insert
Cells(myRow + 1, 5).EntireRow.Insert
Range("A" & myRow + 1 & ":Q" & myRow + 1).Interior.ColorIndex =
15
myRow = myRow + 2
LastRowColE = LastRowColE + 2
End If
myRow = myRow + 1
i = i + 1
Wend

For i = 11 To LastRowColE
If Cells(i, 5).Value = "" Then
If Cells(i - 2, 1).Value <> "" Then
Cells(i, 7).Value = "=Sum(" & Cells(i - 1, 7).Address & ":"
& _
Cells(i - 1, 7).End(xlUp).Address & ")"
Cells(i, 9).Value = "=Sum(" & Cells(i - 1, 9).Address & ":"
& _
Cells(i - 1, 9).End(xlUp).Address & ")"
Cells(i, 11).Value = "=Sum(" & Cells(i - 1, 11).Address &
":" & _
Cells(i - 1, 11).End(xlUp).Address & ")"
Cells(i, 13).Value = "=Sum(" & Cells(i - 1, 13).Address &
":" & _
Cells(i - 1, 13).End(xlUp).Address & ")"
Cells(i, 15).Value = "=Sum(" & Cells(i - 1, 15).Address &
":" & _
Cells(i - 1, 15).End(xlUp).Address & ")"
Cells(i, 17).Value = "=Sum(" & Cells(i - 1, 17).Address &
":" & _
Cells(i - 1, 17).End(xlUp).Address & ")"
i = i + 1
ElseIf Cells(i - 2, 1).Value = "" Then
Cells(i, 7).Value = Cells(i - 1, 7).Value
Cells(i, 9).Value = Cells(i - 1, 9).Value
Cells(i, 11).Value = Cells(i - 1, 11).Value
Cells(i, 13).Value = Cells(i - 1, 13).Value
Cells(i, 15).Value = Cells(i - 1, 15).Value
Cells(i, 17).Value = Cells(i - 1, 17).Value
i = i + 1
End If
End If
Next

For i = 11 To LastRowColE
If Cells(i, 7).Value = "" Then
Cells(i, 7).EntireRow.Delete
End If
Next

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

End Sub

 

[Mark Ivey]

Man... I tried to accommodate the text wrapping for the newsgroup...

Looks like it did it anyway...

Watch out for the lines that wrapped back around..

Mark Ivey