Sub SortSumDelete() LR = Cells(Rows.Count, "A").End(xlUp).Row Set Rng = Range("A7:G" & LR) With ActiveWorkbook.Worksheets(1).Sort .SortFields.Clear .SortFields.Add Key:=Range( _ "B8"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal .SetRange Rng .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With J = 8 Direction = Cells(J, 1).Value Do While Direction <> "" Do While Cells(J + 1, "B").Value = Cells(J, "B").Value Cells(J, "E").Value = Cells(J + 1, "E").Value + Cells(J, "E").Value Cells(J, "G").Value = Cells(J + 1, "G").Value + Cells(J, "G").Value Rows(J + 1).Delete Loop J = J + 1 Direction = Cells(J, 1).Value Loop End Sub
=SE(E(A8>=149;A8<=151);150;SE(O(A8=250;A8=251);250;A8))
=SE.ERRORE(INDICE($B$8:$B$16;AGGREGA(15;6;(RIF.RIGA($B$8:$B$16)-RIF.RIGA($B$8)+1)/(FREQUENZA(CONFRONTA($B$8:$B$16;$B$8:$B$166;0);RIF.RIGA($B$8:$B$16)-RIF.RIGA($B$8)+1)>0);RIGHE($C$8:$C8)));"")
=INDICE($C$8:$C$16;CONFRONTA(A19;$B$8:$B$16;0))
=INDICE($D$8:$D$16;CONFRONTA(A19;$B$8:$B$16;0))
=SOMMA.SE($C$8:$C$16;C19;$H$8:$H$16)
=SE.ERRORE(INDICE($B$8:$B$16;CONFRONTA(0;INDICE(CONTA.SE($J$18:$J18;$B$8:$B$16&""););0));"")
Sub CopiaIncolla() LR = Cells(Rows.Count, "A").End(xlUp).Row numvuote = WorksheetFunction.CountBlank(Range("B8:B" & LR)) numpiene = LR - numvuote Range("A8:G" & numpiene).Copy Range("A8:G" & numpiene).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False Range("A" & numpiene + 1 & ":G" & LR).ClearContents LR = Cells(Rows.Count, "A").End(xlUp).Row Set Rng = Range("A7:G" & LR) With ActiveWorkbook.Worksheets(1).Sort .SortFields.Clear .SortFields.Add Key:=Range( _ "B8"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal .SetRange Rng .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With J = 8 Direction = Cells(J, 1).Value Do While Direction <> "" Do While Cells(J + 1, "B").Value = Cells(J, "B").Value Cells(J, "E").Value = Cells(J + 1, "E").Value + Cells(J, "E").Value Cells(J, "G").Value = Cells(J + 1, "G").Value + Cells(J, "G").Value Rows(J + 1).Delete Loop J = J + 1 Direction = Cells(J, 1).Value Loop End Sub
Ho scritto nuovamente 150 - 150 -149 e ...