Accorpare macro per scrivere e cancellare colonne

[ikwae]

Ciao a tutti… Ho una oceanica mole di dati da produrre ma ho delle macro un po' lente.
E dopo una giornata di lavoro ho prodotto molto poco.

Chiedo aiuto a tutto il Forum di accorpare tre macro e, nello stesso tempo, se possibile velocizzarle.

Sono macro molto semplici con poche righe di codice. Anche se molto semplici descrivo passo-passo
cosa dovrebbe fare la macro, accorpata delle tre macro, di aiuto che cerco:

1a) Spostarsi sul foglio “Ordinati” percorrere tutta la colonna B da B2:B(end)
2b) Dalla stringa selezionata o appena letta;
3c) Recuperare la 1à chiave di ricerca nella stringa è [BA_Gr2_C-01 - 009 ambo Bari] la parte in blu
4d) Recuperare la 2à chiave di ricerca nella stringa è [BA_Gr2_C-01 - 009 ambo Bari] la parte in marrone
5e) Concatenare le due chiavi [BA_Gr2_C-01 Bari]
6f) Spostarsi sul foglio “Riferimento” percorrere tutta la colonna C da C2:C65358 (range fisso)
7g) Confrontare la stringa [BA_Gr2_C-01 Bari] con ognuna delle stringhe della colonna C
8h) Trovata la stringa “gemella” si legge la cella a sx e si memorizza (è il nome della colonna)
9i) Si passa sul foglio “6534_Tutte” e si accoda, nella colonna memorizzata, la stringa letta in “Ordinati”

Si completa tutto il giro fintanto che ci sono stringhe sul foglio “Ordinati” e dopo interviene la 2à macro
10j) Copiare il range fisso A4:A6539 dal foglio “Ordinati” e trasporlo incollandolo in A1 del foglio “6534_Tutte”

Prima le abbiamo riempite le colonne e adesso eliminiamo quelle che non servono e subentra la 3à macro
molto semplice anche lei con pochissime righe di codice. Io sul listato ho risolto commentando e de commentando
le righe di codice ma se possibile fare un automatismo in questo modo:
11k) Impostare il criterio Conta= (da 2 a 6)
12l) Eliminare tutte le colonne che non rientrano nel criterio [Conta]
13m) fine macro

Aggiungo qualche chiarimento sul criterio [Conta] anche se il listato dà già qualche indicazione.

Il criterio [Conta] vuol dire quante stringhe devono rimanere in colonna (oltre l’intestazione).

Esempio_1 se imposto il criterio Conta= 2 cancellerà tutte le colonne
-che hanno solo l’intestazione
-che hanno solo intestazione e 1 stringa

Esempio_2 se imposto il criterio Conta= 6 cancellerà tutte le colonne
-che hanno solo l’intestazione
-che hanno solo intestazione e 1 stringa
-che hanno solo intestazione e 2 stringhe
-che hanno solo intestazione e 3 stringhe
-che hanno solo intestazione e 4 stringhe
-che hanno solo intestazione e 5 stringhe


in allegato un file con 4 fogli;
-uno “Ordinati” Con le stringhe da copiare nelle colonne assegnate
-uno “Riferimento” Con le stringhe “radice” e relativa cella a sx con la colonna assegnata alla stringa
-uno “6534_Tutte” Per mandare in esecuzione la macro “merge” di aiuto
-uno “6534_Tutte_Fine” con risultato finale di cosa si vuole ottenere. Il è impostato Conta=4

Note: sul foglio “6534_Tutte” ci sono due bandiere per visione della struttura del riempimento
delle colonne (6.534 dalla C alla IQJ) e, nel contempo, cosa dovrebbe cancellare la macro Conta.

Ringraziando anticipatamente tutti coloro che mi possono aiutare. 73 ikwae

http://www.filedropper.com/96534accorpa ... olonnerete

[Anthony47]

La mia proposta e' questa macro, che dovrebbe essere il merge tra le tue tre:

Codice: Seleziona tutto

Sub BBB_Le_6534_Fai_Tu()
 Dim UCella As String
 Dim radice As String
 Dim RuFinAnno As String
 Dim CL As Range
'' Dim CL1 As Range
 
Dim myMatch, cCL, mySplit, dBg As Boolean, oArr(), Conta As Long
Dim UB1 As Long, UB2 As Long, cCnt As Long, myTim As Single, Zona
Dim CumM As Long, cCol As Long, I As Long, J As Long
'
Conta = 4                   '<<<<
'
myTim = Timer
dBg = False
Set Zona = Worksheets("Riferimento").Range("C2:C6535")
Sheets("Ordinati").Select
ReDim oArr(0 To 100, 1 To Range("A4").End(xlDown).Row)
UCella = Range("B2").End(xlDown).Address
For Each CL In Range("B2:" & UCella)
    cCL = CL.Value
    myMatch = 0
    mySplit = Split(cCL & " ##", " ", , vbTextCompare)
    radice = mySplit(0) & " " & mySplit(UBound(mySplit) - 1)
    'SETTA DOVE CONFRONTARE
    CumM = 0
    Do
        If IsError(myMatch) Then Exit Do
        Set Zona = Worksheets("Riferimento").Range("C" & (2 + CumM) & ":C6536")    'WWWWW
        myMatch = Application.Match(radice, Zona, False)
        If Not IsError(myMatch) Then
            CumM = CumM + myMatch
            cCol = Range(Zona.Cells(myMatch, 1).Offset(0, -1).Value & 1).Column
            oArr(oArr(0, cCol) + 1, cCol) = cCL
            oArr(0, cCol) = oArr(0, cCol) + 1
            If dBg Then Debug.Print Sheets("6534_Tutte").Range(Zona.Cells(myMatch, 1).Offset(0, -1) & Rows.Count).End(xlUp).Offset(1, 0).Address, CL.Row
        End If
    Loop
    DoEvents
    If CL.Row > 100000 Then Exit For
Next        'CL
Sheets("6534_Tutte").Select
Debug.Print "Fine B: " & Format(Timer - myTim, "0.00") ', CL.Row
'
UB2 = UBound(oArr, 2)
UB1 = UBound(oArr)
For I = 1 To UB2
    If oArr(0, I) >= Conta Or I < 3 Then
        cCnt = cCnt + 1
        oArr(0, I) = Sheets("ordinati").Range("A4").Cells(I, 1)
        For J = 0 To UB1
            oArr(J, cCnt) = oArr(J, I)
            If oArr(J, cCnt) = "" Then Exit For
        Next J
    End If
Next I
Range("A1").Resize(UB1, UB2).ClearContents
Range("A1").Resize(UB1, cCnt).Value = oArr
Debug.Print "Fine BB: " & Format(Timer - myTim, "0.00") ', CL.Row
MsgBox ("Completato; num colonne: " & cCnt)
End Sub


In testa c'e' il parametro "Conta" (ora settato a 4) che deve essere compilato come da desiderata.
Il miglioramento rispetto alla tua combinazione e' visibile.

Prova anche tu.
Attenzione che su foglio 6534_Tutte viene cancellata un'area di 100 righe * Num di colonne calcolata su Foglio(Ordinati).Colonna A, indipendentemente dalla dimensione dei risultati finali

Ciao

[ikwae]

Grazie per aver risposto gentile come sempre... Ho collaudato la macro è super veloce è stupenda magnifica
in pochissimi istanti ci sono tutti i dati in colonna ordinati con le colonne del [Conta] mancanti è giusto bene.

Con un controllo più accurato e contando le stringhe che dovrebbero essere 5.466 mi sono accorto, dopo
che la macro ha finito con Conta=4, che sono 5.486 quindi 17 stringhe in più.
Sono andato a trovarle e le ho trovate tutte e 17.

Quindi ho capito che le macro incolla sulla colonna le stringhe giuste e di appartenenza della colonna
ma, alcune volte, non si sa come o quando, sempre le ultime una due o tre stringhe e come se li tenesse in
memoria, dopo averle scritte bene nella sua colonna li scrive, lasciando una cella libera, in una
colonna che non ha nulla a che vedere con quelle stringhe.

Puoi vedere se mandi in esecuzione (Conta=4) la macro di aiuto sul foglio dell’allegato del primo
post vedrai che nelle colonne; L, DF, HR, HN, LA ecc. (sono 13 colonne) ci sono queste anomalie
ossia le stringhe giuste della colonna poi sotto una cella vuota e sotto 1 o 2 o 3 stringhe estranee.

Ho provato anche con il criterio Conta con diversi tagli ma facendo scorrere le colonne si vede
subito il gruppo di stringhe “buone” e poi una cella vuota e sotto altre stringhe errate (estranee).

Siamo in periodo di ferie quindi se hai tempo e voglia e la puoi mettere a posto va bene altrimenti
anche a settembre che va bene lo stesso.

Ringraziandoti per il lavoro fin qui svolto cordialmente ikwae

[Anthony47]

Con un controllo più accurato e contando le stringhe che dovrebbero essere 5.466 mi sono accorto, dopo che la macro ha finito con Conta=4, che sono 5.486 quindi 17 stringhe in più.

Quindi ho capito che le macro incolla sulla colonna le stringhe giuste e di appartenenza della colonna
ma, alcune volte, non si sa come o quando, sempre le ultime una due o tre stringhe e come se li tenesse in
memoria, dopo averle scritte bene nella sua colonna li scrive, lasciando una cella libera, in una
colonna che non ha nulla a che vedere con quelle stringhe

Eh gia'... Togli questa istruzione:

Codice: Seleziona tutto

        For J = 0 To UB1
            oArr(J, cCnt) = oArr(J, I)
            'If oArr(J, cCnt) = "" Then Exit For             '<----TOGLIERE
        Next J


Impieghera' qualche millisecondo in piu', pazienza.

Puoi anche togliere il DoEvents, che mi serviva durante il debug:

Codice: Seleziona tutto

    Loop
'    DoEvents                          '<---- Si puo' togliere
    If CL.Row > 100000 Then Exit For


Questo recuperera' ampiamente i millisecondi persi sopra

Ciao

[ikwae]

Gentilissimo Anthony … Con la modifica sembra tutto ok e i conti delle stringhe tornano.
Come velocità sembra identica a prima.

Ho letto la magnifica macro che hai sviluppato e, bene o male, riesco a “leggerla” tutta
ma due righe mi confondono, le poche cose già confuse che ho, una è questa riga
[ReDim oArr(0 To 100, 1 To Range("A4").End(xlDown).Row)]

So che Redim è un programmino che ha, esclusivamente, il compito di creare un “orticello”
all’interno della memoria dove Excel va a “coltivare” i dati in funzione delle righe di codice scritte.

Quindi si è creata una matrice dinamica, bidimensionale, di nome oArr che crea un orticello, in
memoria, di 101 righe e 6.534 colonne.

Mentre per le colonne capisco da dove arrivano per le righe non capisco e la domanda è:
il numero delle righe (101) sono legate alle massime righe che si possono scrivere sul
foglio “6534_Tutte” oppure è un dato fittizio attribuito per elaborare “comodamente” i dati in
memoria oppure ha un criterio particolare?

La seconda riga è [mySplit = Split(cCL & " ##", " ", , vbTextCompare)] io la funzione Split
la conosco e la uso di tanto in tanto. So che la scritta all’interno della prima virgola
dovrebbe essere il nome della stringa. Qui, se non erro, la stringa viene concatenata a
uno spazio e, all'interno dello spazio, un doppio cancelletto l’unica spiegazione che riesco a dare che è stato
aggiunto lo spazio e i cancelletti per avere un riferimento certo in memoria?

Se, con moltissima calma, lunedì (oggi è domenica) puoi darmi qualche semplice indicazione, per avere
certezze sul listato, te ne sarei grato.

Ringraziandoti mille e mille volte per il tuo lavoro e l’aiuto, tanto apprezzato, che mi hai dato
cordialmente ikwae

[Anthony47]

OArr e' una matrice che uso per memorizzare i dati intermedi man mano che li cerco e successivamente per filtrare secondo il valore di Conta.
100 e' il numero max di risultati che ho previsto nelle colonne; ho visto che ci teniamo ben lontati da questo limite, quindi non ho previsto di "allungare" la matrice alla bisogna (oggi il supero di questo limite si tradurra' in un errore run time); quindi Sì, 100 e' il numero massimo di righe che si possono scrivere sul foglio “6534_Tutte”
Se pensi sia un problema, o allunghiamo passando da 100 a 200 (ti mangera' qualche Mbyte in piu' di Ram) o inseriamo un meccanismo di ridimensionamento automatico (con qualche secondo in piu' di esecuzione).

Uso Split per "tagliare" in spezzoni la stringa di partenza per poi unire primo e ultimo e creare il valore "radice"; l'uso di & " ##" serve per essere certo che anche in presenza di cella vuota si riesca a creare un "radice"

Ciao

[ikwae]

Gentilissimo Anthony … quindi per oArr è ok solitamente ci sono fino a 12 righe e con lo “storico”
arrivo massimo a 20 righe quindi credo che siamo messi bene… Per lo Split farò alcune prove perché
è importante sapere con esattezza cosa succede dato che sono sempre alle prese con queste stringhe.
Per dire il vero non ci possono essere celle vuote ma le aggiungo io per vedere e studiare i risultati.

Quindi il post è felicemente completato. Rimangono solo i dovuti ringraziamenti oltre per la macro anche
per le preziose info supplementari … Quindi grazie di cuore per tutto il tuo aiuto cordialmente ikwae