ciao,
Ovviamente non volevo copia del cartaceo ma un tuo file excel con piu' dati per verificare il codice che ho scritto
Senza dati riservati e' scontato!
Moderatori: Anthony47, Flash30005
' Call Cancella_Righe ' <<====== QUESTA MACRO va eseguita solo dopo aver fatto le verifiche
Option Explicit
Public UR As Long, I As Long
Sub Elabora_e_Cancella()
Call Copia_e_Imposta_Formati
UR = Range("E" & Rows.Count).End(xlUp).Row
Range("A2:S" & UR).Select
Selection.AutoFilter Field:=5, Criteria1:="<>"
ActiveSheet.ShowAllData
Range("N2:S" & UR).Clear
Range("N2") = "N. Docum."
Range("O2") = "Progr. Docum."
Range("P2") = "Data Scadenza"
Range("Q2") = "Chiave"
Range("R2") = "Righe Duplicate"
Range("S2") = "Progr. Iniziale"
Range("S3") = 1
Range("S4") = 2
Range("S3:S4").Select
Selection.AutoFill Destination:=Range("S3:S" & UR), Type:=xlFillDefault
UR = Range("E" & Rows.Count).End(xlUp).Row
For I = 3 To UR
If Cells(I, "B") = "" Then
Cells(I, "N") = Cells(I - 1, "N")
Cells(I, "O") = Cells(I - 1, "O") + 1
Else
Cells(I, "N") = Cells(I, "B")
Cells(I, "O") = 0
End If
Cells(I, "P") = Cells(I, "E")
Cells(I, "Q") = Cells(I, "N") & "-" & Cells(I, "O")
Next I
Range("R3:R" & UR).FormulaR1C1 = "=COUNTIF(RC[-1]:R100C[-1], RC[-1])"
Range("A2:S" & UR).Select
Selection.Sort Key1:=Range("Q3"), Order1:=xlAscending, Key2:=Range("R3") _
, Order2:=xlAscending, Header:=xlYes, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
:=xlSortNormal
Range("R3:R" & UR).Copy
Range("R3").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("N:S").EntireColumn.AutoFit
UR = Range("E" & Rows.Count).End(xlUp).Row
Selection.AutoFilter Field:=5, Criteria1:="<>"
ActiveSheet.ShowAllData
Selection.AutoFilter Field:=18, Criteria1:=">1", Operator:=xlAnd
Range("N3:S" & UR).Interior.ColorIndex = 6
ActiveSheet.ShowAllData
'..................................................................................................
' Call Cancella_Righe ' <<====== QUESTA MACRO va eseguita solo dopo aver fatto le verifiche
'..................................................................................................
Application.ScreenUpdating = True
MsgBox "Elaborazione Effettuata"
End Sub
Sub Copia_e_Imposta_Formati()
Sheets("Volume affari").Select
Cells.Delete Shift:=xlUp
With Cells
.VerticalAlignment = xlCenter
.Interior.ColorIndex = xlNone
End With
Sheets("Provvigioni contabilizzate").Select
Columns("A:G").Select
Selection.Copy
Sheets("Volume affari").Select
Columns("A:A").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("C:C,E:E").Select
Selection.NumberFormat = "m/d/yyyy"
Range("A1").Copy
Range("B1:G1").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Columns("A:G").Select
Columns("A:G").EntireColumn.AutoFit
End Sub
Sub Cancella_Righe()
Sheets("Volume affari").Select
UR = Range("E" & Rows.Count).End(xlUp).Row
Range("A2:S" & UR).Select
Selection.AutoFilter Field:=5, Criteria1:="<>"
ActiveSheet.ShowAllData
Selection.AutoFilter Field:=18, Criteria1:=">1", Operator:=xlAnd
UR = Range("B" & Rows.Count).End(xlUp).Row
If UR > 2 Then
Rows("3:" & UR).Delete Shift:=xlUp
End If
ActiveSheet.ShowAllData
Selection.AutoFilter
UR = Range("E" & Rows.Count).End(xlUp).Row
Range("A2:S" & UR).Select
Selection.Sort Key1:=Range("S3"), Order1:=xlAscending, Key2:=Range("R3") _
, Order2:=xlAscending, Header:=xlYes, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
:=xlSortNormal
Range("N2:S" & UR).Clear
Range("A2").Select
End Sub
UR = Range("E" & Rows.Count).End(xlUp).Row
Selection.AutoFilter Field:=5, Criteria1:="<>"
ActiveSheet.ShowAllData
Range("A2:S" & UR).Select ' <<====== AGGIUNTA !!!
Selection.AutoFilter Field:=18, Criteria1:=">1", Operator:=xlAnd
Range("N3:S" & UR).Interior.ColorIndex = 6
ActiveSheet.ShowAllData
'..................................................................................................
' Call Cancella_Righe ' <<====== QUESTA MACRO va eseguita solo dopo aver fatto le verifiche
'..................................................................................................
Selection.AutoFilter Field:=18, Criteria1:=">1", Operator:=xlAnd
Selection.Sort Key1:=Range("S3"), Order1:=xlAscending, Key2:=Range("R3") _
, Order2:=xlAscending, Header:=xlYes, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
:=xlSortNormal
' Range("N2:S" & UR).Clear ' <<===== TOGLI l'apice a questa istruzione dopo le verifiche
Range("A2").Select
Range("N2:S" & UR).Clear
Sub Elabora_e_Cancella()
' Questa macro copia i dati, trova le fatture duplicate ed infine "cancella" le fatture duplicate
Call Elabora_0
Call Cancella_Righe
[A2].Select
MsgBox "Elaborazione effettuata. Sono state cancellate tutte le fatture duplicate ", vbInformation
End Sub
Sub Elabora_0()
Call Copia_e_Imposta_Formati
Sheets("Volume affari").Select
UR = Range("E" & Rows.Count).End(xlUp).Row
Range("A2:S" & UR).Select
Selection.AutoFilter Field:=5, Criteria1:="<>"
ActiveSheet.ShowAllData
Range("N2:S" & UR).Clear
Range("N2") = "N. Docum."
Range("O2") = "Progr. Docum."
Range("P2") = "Data Scadenza"
Range("Q2") = "Chiave"
Range("R2") = "Righe Duplicate"
Range("S2") = "Progr. Iniziale"
Range("S3") = 1
Range("S4") = 2
Range("S3:S4").Select
Selection.AutoFill Destination:=Range("S3:S" & UR), Type:=xlFillDefault
UR = Range("E" & Rows.Count).End(xlUp).Row
For I = 3 To UR
If Cells(I, "B") = "" Then
Cells(I, "N") = Cells(I - 1, "N")
Cells(I, "O") = Cells(I - 1, "O") + 1
Else
Cells(I, "N") = Cells(I, "B")
Cells(I, "O") = 0
End If
Cells(I, "P") = Cells(I, "E")
Cells(I, "Q") = Cells(I, "N") & "-" & Cells(I, "O")
Next I
Range("R3:R" & UR).FormulaR1C1 = "=COUNTIF(RC[-1]:R100C[-1], RC[-1])"
Range("A2:S" & UR).Select
Selection.Sort Key1:=Range("Q3"), Order1:=xlAscending, Key2:=Range("R3") _
, Order2:=xlAscending, Header:=xlYes, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
:=xlSortNormal
Range("R3:R" & UR).Copy
Range("R3").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("N:S").EntireColumn.AutoFit
UR = Range("E" & Rows.Count).End(xlUp).Row
Selection.AutoFilter Field:=5, Criteria1:="<>"
ActiveSheet.ShowAllData
Selection.AutoFilter
Range("A2:S" & UR).Select
Selection.AutoFilter Field:=18, Criteria1:=">1", Operator:=xlAnd
Range("N3:S" & UR).Interior.ColorIndex = 6
ActiveSheet.ShowAllData
Application.ScreenUpdating = True
End Sub
Sub Copia_e_Imposta_Formati()
Sheets("Volume affari").Select
Cells.Delete Shift:=xlUp
With Cells
.VerticalAlignment = xlCenter
.Interior.ColorIndex = xlNone
End With
Sheets("Provvigioni contabilizzate").Select
Columns("A:G").Select
Selection.Copy
Sheets("Volume affari").Select
Columns("A:A").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("C:C,E:E").Select
Selection.NumberFormat = "m/d/yyyy"
Range("A1").Copy
Range("B1:G1").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Columns("A:G").Select
Columns("A:G").EntireColumn.AutoFit
Sheets("Provvigioni contabilizzate").Select
[A2].Select
End Sub
Sub Cancella_Righe()
Sheets("Volume affari").Select
UR = Range("N" & Rows.Count).End(xlUp).Row
Range("N2:S" & UR).Select
Selection.AutoFilter Field:=1, Criteria1:="<>"
ActiveSheet.ShowAllData
Selection.AutoFilter
Selection.AutoFilter Field:=5, Criteria1:=">1", Operator:=xlAnd
UR = Range("N" & Rows.Count).End(xlUp).Row
If UR > 2 Then
Rows("3:" & UR).Delete Shift:=xlUp
End If
ActiveSheet.ShowAllData
Selection.AutoFilter
UR = Range("E" & Rows.Count).End(xlUp).Row
Range("A2:S" & UR).Select
Selection.Sort Key1:=Range("S3"), Order1:=xlAscending, Key2:=Range("R3") _
, Order2:=xlAscending, Header:=xlYes, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
:=xlSortNormal
' Range("N2:S" & UR).Clear ' <<===== TOGLI l'apice a questa istruzione dopo le verifiche
Range("A2").Select
End Sub
Torna a Applicazioni Office Windows
Inserire 2 valori nella stessa cella Autore: Ricky0185 |
Forum: Applicazioni Office Windows Risposte: 2 |
Totale valori di più colonne del foglio con funzione Sum Autore: Ricky0185 |
Forum: Applicazioni Office Windows Risposte: 6 |
Excel - Trasposizione e ripetizione di valori Autore: EnricoBanco |
Forum: Applicazioni Office Windows Risposte: 2 |
Visitano il forum: Nessuno e 63 ospiti