Valutazione 4.87/ 5 (100.00%) 5838 voti

Condividi:        

[EXCEL] Eliminare righe che contengono un valore

Vuoi potenziare i tuoi documenti Word? Non sai come si fa una macro in Excel? Devi creare una presentazione in PowerPoint?
Oppure sei passato a OpenOffice e non sei sicuro di come lavorare al meglio?

Moderatori: Anthony47, Flash30005

Re: [EXCEL] Eliminare righe che contengono un valore

Postdi ipsoware » 03/05/13 08:45

Hai provato con più parole contemporaneamente?
ipsoware
Utente Junior
 
Post: 40
Iscritto il: 01/05/13 07:25
Località: Viterbo

Sponsor
 

Re: [EXCEL] Eliminare righe che contengono un valore

Postdi scossa » 03/05/13 08:52

ipsoware ha scritto:Hai provato con più parole contemporaneamente?


Cosa intendi dire?

Le parole sono le 30 dell'array.

Costa troppo allegare il file excel e spendere qualche parola in più per descrivere più dettagliatamente il problema?
Bye!
scossa

Se tu hai una mela, e io ho una mela, e ce le scambiamo, allora tu ed io abbiamo sempre una mela per uno. Ma se tu hai un'idea, ed io ho un'idea, e ce le scambiamo, allora abbiamo entrambi due idee. (George Bernard Shaw)
Avatar utente
scossa
Utente Senior
 
Post: 424
Iscritto il: 01/04/12 16:40
Località: Provincia di Verona

Re: [EXCEL] Eliminare righe che contengono un valore

Postdi ipsoware » 03/05/13 09:30

Ho applicato la Macro ed effettivamente qualche riga viene eliminata ma solo 55 record.
Ne dovrebbe eliminare decine di migliaia. Quindi qualcosa non funziona. Non vorrei che a te funziona perchè hai provato solo con una parola. Forse non riconosce maiuscole minuscole o gli eventuali spazi prima e dopo la parola di ricerca?
Comunque se riuscissi a risolvere il problema te ne sarei grato. :roll:
ipsoware
Utente Junior
 
Post: 40
Iscritto il: 01/05/13 07:25
Località: Viterbo

Re: [EXCEL] Eliminare righe che contengono un valore

Postdi scossa » 03/05/13 10:09

ipsoware ha scritto:Ho applicato la Macro ed effettivamente qualche riga viene eliminata ma solo 55 record.
Ne dovrebbe eliminare decine di migliaia. Quindi qualcosa non funziona. Non vorrei che a te funziona perchè hai provato solo con una parola. Forse non riconosce maiuscole minuscole o gli eventuali spazi prima e dopo la parola di ricerca?
Comunque se riuscissi a risolvere il problema te ne sarei grato. :roll:


L'unica possibilità di risolvere il problema è mettere a disposizione il file reale. Tutto il resto è solo una perdita di tempo, mio per chiedere chiarimenti e tuo per fornirli, perché magari tu parli di mele e io di pere.
Bye!
scossa

Se tu hai una mela, e io ho una mela, e ce le scambiamo, allora tu ed io abbiamo sempre una mela per uno. Ma se tu hai un'idea, ed io ho un'idea, e ce le scambiamo, allora abbiamo entrambi due idee. (George Bernard Shaw)
Avatar utente
scossa
Utente Senior
 
Post: 424
Iscritto il: 01/04/12 16:40
Località: Provincia di Verona

Re: [EXCEL] Eliminare righe che contengono un valore

Postdi ipsoware » 03/05/13 10:25

E' un pò grande però te lo zippo:
http://www.ecartucce.it/Output.zip

Grazie
ipsoware
Utente Junior
 
Post: 40
Iscritto il: 01/05/13 07:25
Località: Viterbo

Re: [EXCEL] Eliminare righe che contengono un valore

Postdi scossa » 03/05/13 10:44

ipsoware ha scritto:E' un pò grande però te lo zippo:
http://www.ecartucce.it/Output.zip

Grazie


Un ultimo chiarimento: confermi che le parole da cercare sono le 30 di quell'array e che vanno cercate solo in colonna D "Descrizione_breve"?
Bye!
scossa

Se tu hai una mela, e io ho una mela, e ce le scambiamo, allora tu ed io abbiamo sempre una mela per uno. Ma se tu hai un'idea, ed io ho un'idea, e ce le scambiamo, allora abbiamo entrambi due idee. (George Bernard Shaw)
Avatar utente
scossa
Utente Senior
 
Post: 424
Iscritto il: 01/04/12 16:40
Località: Provincia di Verona

Re: [EXCEL] Eliminare righe che contengono un valore

Postdi ipsoware » 03/05/13 11:05

Si certo.
ipsoware
Utente Junior
 
Post: 40
Iscritto il: 01/05/13 07:25
Località: Viterbo

Re: [EXCEL] Eliminare righe che contengono un valore

Postdi Anthony47 » 03/05/13 13:28

La macro di scossa non rileva eventuali ripetizioni della stesso termine nelle righe successive alla prima occorrenza. Potrebbe essere modificata in
Codice: Seleziona tutto
    For J = LBound(Cancella) To UBound(Cancella)
reMatch:                  '******
      vRet = Application.Match("*" & Cancella(J) & "*", rngCol, 0)
      If Not IsError(vRet) Then
        'MsgBox Cancella(j) & ": " & vRet
        rng.Rows(vRet).Delete Shift:=xlUp
        GoTo reMatch     '******
      End If
    Next
Le righe aggiunte sono le due marcate ******.

Personalmente pero' ritengo piu' vantaggioso lavorare sul file di input (il file csv), aprendolo riga per riga, verificando le righe da eliminare e quelle da mantenere, e creando un nuovo file nominato ZCZC_NomeOriginale. A questo punto il tuo processo sara' fatto sul nuovo file.
Credo che questo potrebbe risultare in un tempo di elaborazione drasticamente inferiore rispetto al lavoro fatto sul foglio excel.
Allo scopo potresti usare una macro come questa:
Codice: Seleziona tutto
Sub repulist()
Dim VLine, myMatch, FileToOpen, NomeFile As String, myFlag As Boolean, I As Long
Dim J As Long, K As Long
Dim Cancella(1 To 30) As String
  Cancella(1) = "Nero"
  Cancella(2) = "tamburo"
  Cancella(3) = "MULTILICENZE"
  Cancella(4) = "VMWARE"
  'etc etc
'
FileToOpen = Application.GetOpenFilename("CSV Files (*.csv), *.csv")
If FileToOpen = False Then
    Exit Sub
End If
NomeFile = Right(FileToOpen, Len(FileToOpen) - InStrRev(FileToOpen, "\"))
mycell = FileToOpen
myfile = Replace(FileToOpen, NomeFile, "ZCZC_" & NomeFile)
Close #1: Close #2
Open mycell For Input As #1
Open myfile For Output As #2
'
Do While Not EOF(1)
    Line Input #1, VLine
    For I = 1 To UBound(Cancella)
        If Len(VLine) > Len(Replace(VLine, Cancella(I), "", , , vbTextCompare)) Then
            myFlag = True
            Exit For
        End If
    Next I
    If myFlag = False Then
        Print #2, VLine
        J = J + 1
    Else
        myFlag = False
        K = K + 1
    End If
Loop
'
Close #1
Close #2
'
MsgBox ("Creato file ZCZC_" & NomeFile & vbCrLf & _
    "Record registrati: " & J & vbCrLf & _
    "Record eliminati: " & K)
'
End Sub
Completa la scrittura della matrice Cancella, dove potrai inserire fino a 30 stringhe che, se anche una sola e' presente nella riga del file csv, determinano la non presenza della riga intera dal file prodotto.
Lancia la macro; ti verra' chiesto di identificare il file csv da processare. La riga MsgBox ("Inizio") serve solo per confermare esplicitamente l' avvio di un processo certamente lungo, la cui conclusione sara' segnalata con altro msgbox di riepilogo (per 450 mila righe richiedera' 150-180 secondi).

Fai sapere, ciao.
Anthony
Win7 + Office 2010 Ita; Win 7 + Office 2013 Ita
Xp + Office 2003 Ita
E voi cosa usate? (per istruzioni vedere viewtopic.php?f=26&t=97449)
Avatar utente
Anthony47
Moderatore
 
Post: 13895
Iscritto il: 21/03/06 16:03
Località: Ivrea

Re: [EXCEL] Eliminare righe che contengono un valore

Postdi scossa » 03/05/13 16:20

Anthony47 ha scritto:Personalmente pero' ritengo piu' vantaggioso lavorare sul file di input (il file csv), aprendolo riga per riga, verificando le righe da eliminare e quelle da mantenere, e creando un nuovo file nominato ZCZC_NomeOriginale. A questo punto il tuo processo sara' fatto sul nuovo file.
Credo che questo potrebbe risultare in un tempo di elaborazione drasticamente inferiore rispetto al lavoro fatto sul foglio excel.


Ciao Anthony,

sono perfettamente d'accordo con te, lavorare sul file di input è senz'altro vantaggioso.
Infatti per ottenere tempi non biblici eliminando le righe dal file l'unica strada è ricorrere ai filtri.
La seguente routine impiega circa 130 secondi (di cui 90 per eseguire materialmente l'eliminazione dei record con l'istruzione
rngCanc.EntireRow.Delete) contro i circa 80 secondi della tua (sul mio pc).

Codice: Seleziona tutto
'---------------------------------------------------------------------------------------
' Procedure : CancRighe
' Author    : scossa
'---------------------------------------------------------------------------------------
'
Public Sub CancRighe()
  Dim Cancella(1 To 30) As String
  Dim j As Long, nDel As Long, nDelTot As Long
  Dim rng As Range
  Dim rngDel As Range, rArea As Range
  Dim rngCanc As Range
  Dim ws As Worksheet
  Dim nStart As Single
  Dim nStop As Single
  Dim bCalc As Boolean
  Dim nRet As VbMsgBoxResult
 
  With Application
    bCalc = .Calculation
    .Calculation = xlCalculationManual
    .ScreenUpdating = False
  End With
 
  On Error GoTo CancRighe_Error
 
  Set ws = ActiveSheet
  Set rng = ws.UsedRange


  Cancella(1) = "SYMANT"
  Cancella(2) = "LICENZE"
  Cancella(3) = "MULTILICENZE"
  Cancella(4) = "VMWARE"
  Cancella(5) = "GARANZIA"
  Cancella(6) = "MAINTENAN"
  Cancella(7) = "PREVENTIVE"
  Cancella(8) = "ESTENSIONE"
  Cancella(9) = "SW BTO"
  Cancella(10) = "MCAFEE"
  Cancella(11) = "RED HA"
  Cancella(12) = "WINDOWS SERVER CAL"
  Cancella(13) = "ANTI VIRUS"
  Cancella(14) = "PANDA"
  Cancella(15) = "TREND MICRO"
  Cancella(16) = "NUANCE"
  Cancella(17) = "APPLICATION"
  Cancella(18) = "ENTERPRISE"
  Cancella(19) = "COREL"
  Cancella(20) = "APPLICATIVI"
  Cancella(21) = "VISUAL STDIO"
  Cancella(22) = "- MICROS"
  Cancella(23) = "ADOBE"
  Cancella(24) = "LICENZA"
  Cancella(25) = "VEEAM"
  Cancella(26) = "AGFA"
  Cancella(27) = "MAINTENANCE"
  Cancella(28) = "LOTUS"
  Cancella(29) = "EXCHANGE"
  Cancella(30) = "OBBLIGAT ISS"
 
  nStart = Timer
 
  For j = LBound(Cancella) To UBound(Cancella)
    rng.AutoFilter Field:=4, Criteria1:= _
        "=*" & Cancella(j) & "*"
    Set rngDel = Intersect(rng.Offset(1), rng.SpecialCells(xlCellTypeVisible), ws.Columns(4))
    If Not rngDel Is Nothing Then
      nDel = rngDel.Cells.Count
      Application.StatusBar = "trovate " & Format(nDel, "#,##0") & " righe con: " & Cancella(j)
      If rngCanc Is Nothing Then
        Set rngCanc = rngDel
      Else
        Set rngCanc = Union(rngCanc, rngDel)
      End If
    End If
  Next j
  rng.AutoFilter
  For Each rArea In rngCanc.Areas
    nDelTot = nDelTot + rArea.Rows.Count
  Next
  Application.StatusBar = "elimino le " & Format(nDelTot, "#,##0") & " righe"
  rngCanc.EntireRow.Delete
  nStop = Timer
  Application.StatusBar = "fatto!"
  On Error GoTo 0
  'Exit Sub

CancRighe_Error:
  Set rngCanc = Nothing
  Set rngDel = Nothing
  Set rng = Nothing
  Set ws = Nothing

  With Application
    .Calculation = xlCalculationAutomatic
    .ScreenUpdating = True
    .StatusBar = False
  End With
  If Err.Number <> 0 Then
    MsgBox "Error: " & Err.Description, vbCritical, "ERRORE"
  Else
    MsgBox "elaborazione terminata in" & vbCrLf & nStop - nStart & " secondi" & vbCrLf & _
      "cancellate " & Format(nDelTot, "#,##0") & " righe"
  End If
End Sub


L'unica perplessità sul tuo codice, è che se una delle parole fosse presente su una colonna diversa dalla D in una riga in cui nella cella D la parola non è presente, la riga verrebbe comunque eliminata.
Ho pensato quindi a questa variante che, oltretutto, quasi dimezza i tempi di esecuzione (impiega circa 50 secondi):

Codice: Seleziona tutto
'---------------------------------------------------------------------------------------
' Procedure : repulist
' Author    : Anthony
'---------------------------------------------------------------------------------------
'
Sub repulist()
Dim VLine, myMatch, FileToOpen, NomeFile As String, myFlag As Boolean, I As Long
Dim J As Long, K As Long
Dim x As Long
Dim mycell, myfile
Dim nStart As Single
Dim nStop As Single
Dim sDesc As String
Dim nAt As Long
Dim Cancella(1 To 30) As String
  Cancella(1) = "SYMANT"
  Cancella(2) = "LICENZE"
  Cancella(3) = "MULTILICENZE"
  Cancella(4) = "VMWARE"
  Cancella(5) = "GARANZIA"
  Cancella(6) = "MAINTENAN"
  Cancella(7) = "PREVENTIVE"
  Cancella(8) = "ESTENSIONE"
  Cancella(9) = "SW BTO"
  Cancella(10) = "MCAFEE"
  Cancella(11) = "RED HA"
  Cancella(12) = "WINDOWS SERVER CAL"
  Cancella(13) = "ANTI VIRUS"
  Cancella(14) = "PANDA"
  Cancella(15) = "TREND MICRO"
  Cancella(16) = "NUANCE"
  Cancella(17) = "APPLICATION"
  Cancella(18) = "ENTERPRISE"
  Cancella(19) = "COREL"
  Cancella(20) = "APPLICATIVI"
  Cancella(21) = "VISUAL STDIO"
  Cancella(22) = "- MICROS"
  Cancella(23) = "ADOBE"
  Cancella(24) = "LICENZA"
  Cancella(25) = "VEEAM"
  Cancella(26) = "AGFA"
  Cancella(27) = "MAINTENANCE"
  Cancella(28) = "LOTUS"
  Cancella(29) = "EXCHANGE"
  Cancella(30) = "OBBLIGAT ISS"
  'etc etc
'
FileToOpen = Application.GetOpenFilename("CSV Files (*.csv), *.csv")
If FileToOpen = False Then
    Exit Sub
End If
NomeFile = Right(FileToOpen, Len(FileToOpen) - InStrRev(FileToOpen, "\"))
mycell = FileToOpen
myfile = Replace(FileToOpen, NomeFile, "ZCZC_" & NomeFile)
Close #1: Close #2
Open mycell For Input As #1
Open myfile For Output As #2
'
nStart = Timer
Do While Not EOF(1)
    Line Input #1, VLine
    sDesc = VLine
    For x = 1 To 3
      nAt = InStr(sDesc, ";")
      sDesc = Mid(sDesc, nAt + 1)
    Next x
    nAt = InStr(sDesc, ";")
    sDesc = Left(sDesc, nAt - 1)
    For I = 1 To UBound(Cancella)
        If Len(sDesc) > Len(Replace(sDesc, Cancella(I), "", , , vbTextCompare)) Then
            myFlag = True
            Exit For
        End If
    Next I
    If myFlag = False Then
        Print #2, VLine
        J = J + 1
    Else
        myFlag = False
        K = K + 1
    End If
Loop
'
nStop = Timer
Close #1
Close #2
'
MsgBox ("Creato file ZCZC_" & NomeFile & vbCrLf & _
    "Record registrati: " & J & vbCrLf & _
    "Record eliminati: " & K) & vbCrLf & _
    nStop - nStart & " secondi"
'
End Sub
Bye!
scossa

Se tu hai una mela, e io ho una mela, e ce le scambiamo, allora tu ed io abbiamo sempre una mela per uno. Ma se tu hai un'idea, ed io ho un'idea, e ce le scambiamo, allora abbiamo entrambi due idee. (George Bernard Shaw)
Avatar utente
scossa
Utente Senior
 
Post: 424
Iscritto il: 01/04/12 16:40
Località: Provincia di Verona

Re: [EXCEL] Eliminare righe che contengono un valore

Postdi Anthony47 » 03/05/13 23:28

Il rischio di eliminare righe in piu' del necessario non credo sia reale; infatti nel file d' origine mi pare che il quarto campo sia una specie di superset dei 3 precedenti.
Hai ragione pero' a cercare un algoritmo piu' efficiente: con 450mila righe ogni microsecondo alla fine si sente; e quello che io avevo usato ha la pecca (proprio per il fatto che una stringa se trovata esiste in piu' campi) di lavorare piu' volte [Replace(VLine, Cancella(I), etc etc) esegue due replace, se la stringa in Cancella(I) esiste due volte in VLine].
Stimolato dal tuo esempio ho anche constatato che un ulteriore miglioramento si puo' avere evitando vbTextCompare e usando vbBinaryCompare, previa conversione del testo in maiuscolo.
Infine ho constatato che invece di usare Len e Replace un altro grosso miglioramento si puo' avere usando direttamente InStr; quindi, combinando l' uso di vbBinaryCompare e di InStr il loop principale diventa
Codice: Seleziona tutto
  Do While Not EOF(1)
  Line Input #1, VLine
  VLine2 = UCase(VLine)
  For I = 1 To UBound(Cancella)
    If InStr(1, VLine2, Cancella(I), vbBinaryCompare) > 1 Then
      myFlag = True
      Exit For
    End If
  Next I
  If myFlag = False Then
    Print #2, VLine
    J = J + 1
  Else
    myFlag = False
    K = K + 1
  End If
  Loop

Il tempo di esecuzione e' decisamente interessante.

Ciao a tutti.
Anthony
Win7 + Office 2010 Ita; Win 7 + Office 2013 Ita
Xp + Office 2003 Ita
E voi cosa usate? (per istruzioni vedere viewtopic.php?f=26&t=97449)
Avatar utente
Anthony47
Moderatore
 
Post: 13895
Iscritto il: 21/03/06 16:03
Località: Ivrea

Re: [EXCEL] Eliminare righe che contengono un valore

Postdi ipsoware » 06/05/13 15:01

Non posso che ringraziarvi. Ottimo lavoro siete stati grandi.
Funziona tutto perfettamente.
ipsoware
Utente Junior
 
Post: 40
Iscritto il: 01/05/13 07:25
Località: Viterbo

Re: [EXCEL] Eliminare righe che contengono un valore

Postdi lyonssss » 08/10/13 15:30

ciao Scossa,
volevo solo ringraziarti, avevo un problema simile a ipsoware (molto più semplice in quanto il mio foglio era con poche righe) e con il tuo primo listato l'ho risolto brillantemente (funziona alla grande) !!!
ringrazio anche Antony anche se con il suo primo listato misteriosamente uno dei cancella non veniva preso in considerazione.
Siete dei grandissimi!
Saluti
lyonssss
Newbie
 
Post: 1
Iscritto il: 07/09/10 16:31

Re: [EXCEL] Eliminare righe che contengono un valore

Postdi scossa » 08/10/13 17:50

@lyonssss: grazie a te del cortese riscontro.
Bye!
scossa

Se tu hai una mela, e io ho una mela, e ce le scambiamo, allora tu ed io abbiamo sempre una mela per uno. Ma se tu hai un'idea, ed io ho un'idea, e ce le scambiamo, allora abbiamo entrambi due idee. (George Bernard Shaw)
Avatar utente
scossa
Utente Senior
 
Post: 424
Iscritto il: 01/04/12 16:40
Località: Provincia di Verona

Re: [EXCEL] Eliminare righe che contengono un valore

Postdi Anthony47 » 08/10/13 23:25

Complimenti, ti sei registrato apposta per ringraziare, quindi "prego" (anzi, nel mio caso: "Di niente...") e un doppio "benvenuto nel forum".
Alla prossima...
Anthony
Win7 + Office 2010 Ita; Win 7 + Office 2013 Ita
Xp + Office 2003 Ita
E voi cosa usate? (per istruzioni vedere viewtopic.php?f=26&t=97449)
Avatar utente
Anthony47
Moderatore
 
Post: 13895
Iscritto il: 21/03/06 16:03
Località: Ivrea

Precedente

Torna a Applicazioni Office Windows


Topic correlati a "[EXCEL] Eliminare righe che contengono un valore":


Chi c’è in linea

Visitano il forum: Nessuno e 17 ospiti