Look down column and enter blank row and color grey
 

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...

--
Les

Look down column and enter blank row and color grey
 

Question:

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

Mark Ivey



"Les" wrote in message
...
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...

--
Les

Look down column and enter blank row and color grey
 

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

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

--
Les


"Mark Ivey" wrote:

Question:

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

Mark Ivey



"Les" wrote in message
...
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...

--
Les

Look down column and enter blank row and color grey
 

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/assistan...lconPT101.aspx

Les wrote:

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...

--
Les

--

Dave Peterson

Look down column and enter blank row and color grey
 

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

Look down column and enter blank row and color grey
 

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

"Mark Ivey" (do_not_spam) wrote in message
...
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