Excel - Macro per eliminare dati univoci

Asked By JOE on 02-Nov-10 02:29 PM
ciao a tutti, mi occorrerebbe una macro per eliminare dei dati
univoci...
in cui la colonna di riferimento è la A e vi sono anche delle celle
vuote,
ma deve solo cancellare tutta la riga in colonna B, mi spiego:

colonna A        colonna B
pippo                 pippo
pluto                gastone
clarabella         clarabella
ciccio                 ciccio
tarzan                  bello
fiore
pluto
ciclamino

ma macro deve cancellarmi la riga relativa in colonna B
gastone
bello
fiore
ciclamino

spero non sia un problema..

Giuseppe




elby replied to JOE on 02-Nov-10 07:33 PM
Ciao Giuseppe
Non se so se ho capito bene quello che ti serve. La segunte cancella i
valori nella colonna B senza corrispondenze in colonna A
Da mettere nel modulo del foglio dove vi sono i dati.
Ciao Elio

Sub Test()
Dim rngA As Range
Dim rngB As Range
Dim myCell As Range
Dim col As Collection
Set rngA =3D Range("A1:A5")
Set rngB =3D Range("B1:B8")
Set col =3D New Collection
For Each myCell In rngA
On Error Resume Next
.Add Item:=3D0, Key:=3DmyCell.Value
On Error GoTo 0
Next myCell
For Each myCell In rngB
On Error Resume Next
.Add Item:=3D0, Key:=3DmyCell.Value
If Err.Number <> 0 Then
myCell.ClearContents
On Error GoTo 0
Next myCell
End Sub
elby replied to elby on 02-Nov-10 07:43 PM
Rettifico con:

Sub Test()
Dim rngA As Range
Dim rngB As Range
Dim myCell As Range
Dim col As Collection
Set rngA = Range("A1:A5")
Set rngB = Range("B1:B8")
Set col = New Collection
For Each myCell In rngA
On Error Resume Next
col.Add Item:=0, Key:=myCell.Value
On Error GoTo 0
Next myCell
For Each myCell In rngB
On Error Resume Next
col.Add Item:=0, Key:=myCell.Value
If Err.Number <> 0 Then
myCell.ClearContents
End If
On Error GoTo 0
Next myCell
End Sub
elby replied to elby on 02-Nov-10 07:47 PM
Mi scuso ancora la precedente cancella invece i dati con
corrispondenza. Questa senza corrispondenze:
Sub Test()
Dim rngA As Range
Dim rngB As Range
Dim myCell As Range
Dim col As Collection
Set rngA = Range("A1:A5")
Set rngB = Range("B1:B8")
Set col = New Collection
For Each myCell In rngA
On Error Resume Next
col.Add Item:=0, Key:=myCell.Value
On Error GoTo 0
Next myCell
For Each myCell In rngB
On Error Resume Next
col.Add Item:=0, Key:=myCell.Value
If Err.Number = 0 Then
myCell.ClearContents
End If
On Error GoTo 0
Next myCell
End Sub
JOE replied to elby on 03-Nov-10 11:18 AM
----------------------
grazie Elby, funziona benissimo.. e mi servivano tutte e 2 le
routine,.,.
Ciao
r replied to elby on 03-Nov-10 12:55 PM
ciao elby,
io normalmente utilizzo il dictionary (presente nella potente libreria
scripting) in vece della collection, sempre che non ci sia particolari
criticit=E0 nei tempi. Il dictionary dispone di molti pi=F9 metodi e
propriet=E0 che spesso rendono il codice pi=F9 compatto e chiaro (almeno
per me) oltre a non necessitare praticamente mai di una forzata
gestione degli errori ... posto la routine come la scriverei col dic:

Sub TestDic(rngA As Range, rngB As Range)
Dim myCell As Range
Dim col As Object
Set col =3D CreateObject("scripting.dictionary")
For Each myCell In rngA
col.Item(CStr(myCell.Value)) =3D ""
Next myCell
For Each myCell In rngB
If col.Exists(CStr(myCell.Value)) Then
myCell.ClearContents
End If
Next myCell
End Sub

saluti
r
elby replied to r on 03-Nov-10 02:23 PM
Chiamami pure Elio. Elby =E8 una sorta di peccati giovent=F9 all'atto
della iscrizione al newsgroup
Comincer=F2 ad utilizzare il Dictionary per apprezzarne le potenzialit=E0.
Grazie per il suggerimento
Elio
Mauro Gamberini replied to r on 04-Nov-10 05:53 AM
io normalmente utilizzo il dictionary (presente nella potente libreria
scripting) in vece della collection, sempre che non ci sia particolari
criticit? nei tempi. Il dictionary dispone di molti pi? metodi e
propriet? che spesso rendono il codice pi? compatto e chiaro (almeno
per me) oltre a non necessitare praticamente mai di una forzata
gestione degli errori ... posto la routine come la scriverei col dic:

****************************************

Tutto vero. Con la limitazione che Dictionary non
gestisce oggetti come invece fa la Collection.

Creo una semplice classe con questo codice:

Private s As String

Public Property Let m(ByVal dato As String)
s = dato
End Property

Public Property Get m() As String
m = s
End Property

In un modulo:

Public Sub m_1()

Dim sh As Worksheet
Dim col As Collection
Dim cls As Classe1
Dim lng As Long
Dim v As Variant

Set sh = ThisWorkbook.Worksheets("Foglio1")
Set col = New Collection
With sh
For lng = 1 To 10
Set cls = New Classe1
cls.m = .Range("A" & lng).Value
col.Add cls, CStr(lng)
Next
End With

For Each v In col
MsgBox v.m
Next

Set cls = Nothing
Set col = Nothing
Set sh = Nothing

End Sub

Public Sub m_2()

Dim sh As Worksheet
Dim dic As Dictionary
Dim cls As Classe1
Dim lng As Long
Dim v As Variant

Set sh = ThisWorkbook.Worksheets("Foglio1")
Set dic = New Dictionary
With sh
For lng = 1 To 10
Set cls = New Classe1
cls.m = .Range("A" & lng).Value
dic.Add lng, cls
Next
End With

For Each v In dic
MsgBox v.m
Next

Set cls = Nothing
Set dic = Nothing
Set sh = Nothing

End Sub

Tutto questo pu? sembrare assurdo, ma...
...ma quando si interroga un db e se ne estraggono i dati,
molto spesso si crea un proprio modello di oggetti
(qui la semplice Classe1). A riprova che Dictionary non
gestisce gli oggetti mentre la Collection s?:

Public Sub m_3()

Dim sh As Worksheet
Dim col As Collection
Dim rng As Range
Dim lng As Long
Dim v As Variant

Set sh = ThisWorkbook.Worksheets("Foglio1")
Set col = New Collection
With sh
For lng = 1 To 10
Set rng = .Range("A" & lng)
col.Add rng, CStr(lng)
Next
End With

For Each v In col
MsgBox v.Address & vbNewLine & v.Value
Next

Set rng = Nothing
Set col = Nothing
Set sh = Nothing

End Sub

Public Sub m_4()

Dim sh As Worksheet
Dim dic As Dictionary
Dim rng As Range
Dim lng As Long
Dim v As Variant

Set sh = ThisWorkbook.Worksheets("Foglio1")
Set dic = New Dictionary
With sh
For lng = 1 To 10
Set rng = .Range("A" & lng)
dic.Add lng, rng
Next
End With

For Each v In dic
MsgBox v.Address & vbNewLine & v.Value
Next

Set rng = Nothing
Set dic = Nothing
Set sh = Nothing

End Sub


The Collection object is useful in some situations where the Dictionary
object is not.
For example, if you are creating a custom object model, you can use a
Collection object
to store a reference to a custom collection, but you cannot use a Dictionary
object to do this.

Vabb? dai, siamo in Excel e se ho bisogno di elaborare dati presi
da un db, li spalmo in un foglio e poi li lavoro da l?, eliminando il foglio
quando non serve pi?(ho gi? detto in un altro post cosa succede
a tutto quello che ho in memoria se ho un errore non gestito e la mia
opinione riguardo ad Array, Collection, matrici memorizzate in genere).

Era solo per precisare un limite di Dictionary che ha altri pregi.

---------------------------
Mauro Gamberini
Microsoft MVP - Excel
http://www.riolab.org/
http://www.maurogsc.eu/
r replied to Mauro Gamberini on 04-Nov-10 06:16 AM
On 4 Nov, 10:53, "Mauro Gamberini"
ry
oglio

il ciclo for each viene fatto sulle key ... un dictionary accetta un
variant come key ... e in un variant puoi metterci anche un
elefante ... la tua m_2 va corretta cos=EC:

Public Sub m_2()


Dim sh As Worksheet
Dim dic As Dictionary
Dim cls As Classe1
Dim lng As Long
Dim v As Variant


Set sh =3D ThisWorkbook.Worksheets("Foglio1")
Set dic =3D New Dictionary
With sh
For lng =3D 1 To 10
Set cls =3D New Classe1
cls.m =3D .Range("A" & lng).Value
dic.Add cls, lng
Next
End With

Debug.Print dic.Count
For Each v In dic
MsgBox v.m
Next


Set cls =3D Nothing
Set dic =3D Nothing
Set sh =3D Nothing


End Sub

comunque, se dovessi crearmi un insieme probabilmente sceglierei una
collection o un array in quanto l'insieme non avrebbe a prescindere
elementi doppi ... ma se devo utilizzare un insieme con lo scopo di
usarlo poi come un elenco unico (dizionario appunto) io non ho dubbi
su ci=F2 che userei ...

saluti
r

p.s.
la citazione dove l'hai presa?
r replied to r on 04-Nov-10 06:33 AM
trovata
http://msdn.microsoft.com/en-us/library/aa164502(office.10).aspx
r
Mauro Gamberini replied to r on 04-Nov-10 06:35 AM
On 4 Nov, 10:53, "Mauro Gamberini"

il ciclo for each viene fatto sulle key ... un dictionary accetta un
variant come key ... e in un variant puoi metterci anche un
elefante ... la tua m_2 va corretta cos?:

Certo, lo so. Facevo notare come non sia possibile ciclare l'oggetto.
Vero ? che nella Collection non posso *mirare* ad una chiave.
Pro e contro, per l'uno e per l'altro.


comunque, se dovessi crearmi un insieme probabilmente sceglierei una
collection o un array in quanto l'insieme non avrebbe a prescindere
elementi doppi

Rimanendo agli esempi di prima, si presume
che un db abbia una chiave primaria per il record e che i riferimenti
per le celle siano univoci.... comunque s?, ? cos?.



Segreto istituzionale... ;-)
... vecchio scambio di mail con
un programmatore inglese,
ai gloriosi tempi di Visual Basic 6.0,
che Zio Bill l'abbia in gloria.

---------------------------
Mauro Gamberini
Microsoft MVP - Excel
http://www.riolab.org/
http://www.maurogsc.eu/
r replied to Mauro Gamberini on 04-Nov-10 07:06 AM
On 4 Nov, 11:35, "Mauro Gamberini"


io ho capito ... ma forse gli altri no ...
un ciclo for each non crea problemi perch=E8 tutti gli oggetti (nel caso
del dic memorizzati come key) vengono recuperati correttamente
nell'ordine di caricamento. La Collection =E8 un insieme ordinato per
cui l'indice che corrisponde all'ordine di caricamento pu=F2 essere
utilizzato per il richiamo dell'item. Dictionary =E8 simile a una
matrice associativa perl, e come tale =E8 una struttura non ordinata. E'
quindi impossibile richiamare un suo elemento (key) utilizzando un
indice.

in conclusione =E8 possibile eseguire un ciclo for each non =E8 possibile
eseguire un ciclo for i=3D1 to dic.count.

=E8 altres=EC vero che all'occorrenza (in entrambi i casi) =E8 facile
definire una classe dic o col che consenta metodi non definiti ...
questo allo scopo di risparmiare codice all'uso frequente

come =E8 altres=EC vero che aggiungendo un contatore al ciclo for each
risolveresti facilmente ... sicuramente perdendo in prestazione ... ma
come gi=E0 detto ... chissenefrega in un insieme limitato :-)

ultima cosa ... il metodo add di collection =E8 ad esempio pi=F9 potente
del corrispettivo del dictionary ... quindi vero che vantaggi e
svantaggi vanno valutati ma il suggerimento iniziale era volto a
*scoprire* un oggetto che appunto ritengo comodo e ben fatto
saluti
r
r replied to Mauro Gamberini on 04-Nov-10 07:11 AM
On 4 Nov, 11:35, "Mauro Gamberini"


beh tanto amico non era visto che ti finocchiato cos=EC :-)
mai fidarsi degli inglesi ... (Norman escluso) :-)
ciao
r
Mauro Gamberini replied to r on 04-Nov-10 07:25 AM
beh tanto amico non era visto che ti finocchiato cos? :-)
mai fidarsi degli inglesi ... (Norman escluso) :-)
ciao
**************

ROTFL!
---------------------------
Mauro Gamberini
Microsoft MVP - Excel
http://www.riolab.org/
http://www.maurogsc.eu/
r replied to Mauro Gamberini on 04-Nov-10 07:28 AM
On 4 Nov, 12:25, "Mauro Gamberini"


mi si vede l'aureola 0:-)
Mauro Gamberini replied to r on 04-Nov-10 07:47 AM
On 4 Nov, 11:35, "Mauro Gamberini"


io ho capito ... ma forse gli altri no ...
un ciclo for each non crea problemi perch? tutti gli oggetti (nel caso
del dic memorizzati come key) vengono recuperati correttamente
nell'ordine di caricamento. La Collection ? un insieme ordinato per
cui l'indice che corrisponde all'ordine di caricamento pu? essere
utilizzato per il richiamo dell'item:
********************

Ad esempio, sempre in riferimento a quanto postato in precedenza:

Public Sub m_1()

Dim sh As Worksheet
Dim col As Collection
Dim dic As Dictionary
Dim cls As Classe1
Dim lng As Long

Set sh = ThisWorkbook.Worksheets("Foglio1")
Set col = New Collection
Set dic = New Dictionary

With sh
For lng = 1 To 10
Set cls = New Classe1
cls.m = .Range("A" & lng).Value
col.Add cls, CStr(lng)
dic.Add cls, lng
Next
End With

Set cls = col("5") '<<===
MsgBox cls.m  '<<===

Set cls = Nothing
Set col = Nothing
Set sh = Nothing

End Sub


ultima cosa ... il metodo add di collection ? ad esempio pi? potente
del corrispettivo del dictionary ... quindi vero che vantaggi e
svantaggi vanno valutati ma il suggerimento iniziale era volto a
*scoprire* un oggetto che appunto ritengo comodo e ben fatto
*********

+1

---------------------------
Mauro Gamberini
Microsoft MVP - Excel
http://www.riolab.org/
http://www.maurogsc.eu/
r replied to Mauro Gamberini on 04-Nov-10 08:13 AM
On 4 Nov, 12:47, "Mauro Gamberini"


no intendevo proprio l'indice ... che pu=F2 essere usato quanto la key
ad esempio:
Set cls =3D col(5) '<<=3D=3D=3D

ciao
r
Mauro Gamberini replied to r on 04-Nov-10 09:24 AM
no intendevo proprio l'indice ... che pu? essere usato quanto la key
ad esempio:
Set cls = col(5) '<<===

**********

Uhmmmmmmm.... comunque io passerei alla Key un bel valore
univoco significativo dell'oggetto, esempio l'ID.
Io non mi fido degli indici 1, 2, n.

---------------------------
Mauro Gamberini
Microsoft MVP - Excel
http://www.riolab.org/
http://www.maurogsc.eu/
r replied to Mauro Gamberini on 04-Nov-10 09:41 AM
On 4 Nov, 14:24, "Mauro Gamberini"

forse sono io che non ho capito a pieno il tuo ragionamento, per=F2 non
=E8 un problema memorizzare un oggetto nella key oppure nell'item di un
dictionary ... volendo infatti potresti tranquillamente scrivere:

Sub test()
Dim dic As New Dictionary
Dim rng As Excel.Range
dic.Add [a1].Value, [a1]

Set rng =3D dic([a1].Value)
Debug.Print rng.Value
End Sub

la differenza principale sta nel fatto che come dicevo una collection
=E8 per definizione un insieme di elementi ordinato mentre il dic no ...
e l'ordine =E8 stabilito dall'indice che nella collection pu=F2 essere
usato (come in una matrice seppur a base 1 di defoult) in vece della
key.

saluti
r
Mauro Gamberini replied to r on 04-Nov-10 10:38 AM
forse sono io che non ho capito a pieno il tuo ragionamento,
******************************

Penso che ognuno di noi stia parlando d'altro... ;-)

---------------------------
Mauro Gamberini
Microsoft MVP - Excel
http://www.riolab.org/
http://www.maurogsc.eu/
r replied to Mauro Gamberini on 04-Nov-10 11:43 AM
On 4 Nov, 15:38, "Mauro Gamberini"

magari chi legge capir=E0 entrambi :-)
intanto grazie della chiaccherata
r
Mauro Gamberini replied to r on 04-Nov-10 11:53 AM
On 4 Nov, 15:38, "Mauro Gamberini"

magari chi legge capir? entrambi :-)
intanto grazie della chiaccherata
****

A te.

[OT]Sono a Milano domani e sabato.
Ma l'agenda ? piena di impegni.

---------------------------
Mauro Gamberini
Microsoft MVP - Excel
http://www.riolab.org/
http://www.maurogsc.eu/
r replied to Mauro Gamberini on 04-Nov-10 11:56 AM
On 4 Nov, 16:53, "Mauro Gamberini"
f20g2000yqi.googlegroups.com...

se milano =E8 inteso come segrate ... domani ti invito a pranzo
fai sapere
ciao
r
Mauro Gamberini replied to r on 04-Nov-10 12:02 PM
On 4 Nov, 16:53, "Mauro Gamberini"

se milano ? inteso come segrate ... domani ti invito a pranzo
fai sapere
ciao
***********************************

Sono in MS, prigioniero fino a mezzanotte domani
e sabato vorrei rientrare presto(nebbia).
Eh, ma non te la cavi mica cos?!
Da quelle parti torno 2/3 volte prima delle feste
(al?, anche il 2010 sta volando via... :-( )


---------------------------
Mauro Gamberini
Microsoft MVP - Excel
http://www.riolab.org/
http://www.maurogsc.eu/