Macro to sum mixed ranges using xldown

[Sarah333]

I am trying to create a security report, and each user has multiple entity security and may have different role security which is the far right column "Security Codes", this needs to be matched to a role so the sum of the security codes = Role. therefore I need a macro to go down and sum the rows which apply to each users:
user 1 = (sum Row 1:5)
user 2 = (sum Row 6:9) etc

	user1	Security code result	Security codes
row1	user1	63	61
row2
row3			2
row4
row5
row6	user2	4	3
row7
row8			1
row9
row10	user3	6	4
row11
row12			2

<COLGROUP><COL style="WIDTH: 48pt" span=4 width=64><TBODY>
</TBODY>

I thougth i could utilise some existing code that removes repeated members so the user only shows once, but I'm rusty with VBA and not sure how to change the Active cell select range to use the correct column offset when using the xldown on the username column

thanks very much in advance
Sarah

existing script
-----------------
Application.Calculation = xlCalculationManual
Range("AE5").Select
Selectme = ActiveCell.Value
ActiveCell.Offset(1, 0).Select

Do Until ActiveCell.Value = ""


If ActiveCell.Value = Selectme Then
Selection.Clear

Else: Selectme = ActiveCell.Value

End If
ActiveCell.Offset(1, 0).Select
Loop

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

 

[hurgadion]

Hi,
try to use the macro:

Sub SumSum()
Dim tbl(), a&, i&, x&

a = Cells(Rows.Count, 3).End(xlUp).Row

For i = 1 To a
  If Cells(i, 1).Value <> "" Then
    x = x + 1
    ReDim Preserve tbl(1 To x)
    tbl(x) = i
  End If
Next i

x = x + 1
ReDim Preserve tbl(1 To x)
tbl(x) = a + 1

For i = 1 To UBound(tbl) - 1
   Cells(tbl(i), 5).Value = Application.Sum(Range("C" & tbl(i) & ":C" & tbl(i + 1) - 1))
Next i

End Sub

Best regards.

 

[Sarah333]

thanks I will give that a go

Hi,
try to use the macro:

Sub SumSum()
Dim tbl(), a&, i&, x&

a = Cells(Rows.Count, 3).End(xlUp).Row

For i = 1 To a
  If Cells(i, 1).Value <> "" Then
    x = x + 1
    ReDim Preserve tbl(1 To x)
    tbl(x) = i
  End If
Next i

x = x + 1
ReDim Preserve tbl(1 To x)
tbl(x) = a + 1

For i = 1 To UBound(tbl) - 1
   Cells(tbl(i), 5).Value = Application.Sum(Range("C" & tbl(i) & ":C" & tbl(i + 1) - 1))
Next i

End Sub

Best regards.

 

[Sarah333]

thanks I will give that a go

This doesnt quite work, the number of rows between users is different each time so the xldown need to first determine the number of rows to sum.

so to clarify the sum needs to be in column security code result. the sum is adding column security codes and the macro needs to determin the number of rows to sum by using the xldown feature on the username column as this is different for every user, the code in the far right column only shows a value for each change in security group and if they have more than one group this affects the overall security role detrmined by a lookup (not in this macro)

so it needs to somehow store the row with the first username for the start of the sum (to be in column security result which adds Security code column), do an xldown to the next username and the row above would be the end of the sum.

I hope this explains my requirement more clearly, appreciate your help
Sarah

 

[hurgadion]

I need an example with Data, my English is good not enough now, best regards.

 

[Sarah333]

I need an example with Data, my English is good not enough now, best regards.

this is the first post sample file

 

[hurgadion]

I think that my macro works good for Your Example from the first post... However, I try to understand Your requirement... I need maybe another Example for which my macro works not good... It will for me a fine Hint to understand Your English requirement... Besides, You can change Column 5 to Column 2 to obtain results in the proper places...