VBA: Inserire in un elenco di dati tante righe vuote quante…ne servono

[pic omnic]

Ho un lungo elenco di dati, dalla col A alla col Y, che si estende per circa 4mila righe
Per una migliore organizzazione, ho diviso con una linea rossa, tutte le righe dell'elenco in tanti gruppi, di sette righe ciascuno
Quindi la prima riga di ogni gruppo sarà la riga 1,8,15,22,29,36,43,50 ecc (cioè tutti i multipli di 7+1)
Nelle celle della colonna J ,ogni tanto, compare il valore : Anno …. (cioè i primi 4 caratteri dei dati presenti corrispondono ad Anno). Ogni volta che si incontra la parola “Anno”, mi servirebbe inserire tante righe vuote, quante ne servono per fare in modo che questa riga contenente la parola anno , diventi la prima riga del gruppo successivo, cioè vada a finire nella riga multiplo di 7 (+1) successiva
Se cioè la parola “Anno” si trovasse alla riga 33 occorrerebbe inserire 3 righe vuote per portarla alla riga 36
Se la parola anno si trovasse alla riga 80 ne occorrerebbero 5 righe vuote per arrivare alla riga 85, ecce cc
Qualcuno bravo in matematica (e siete in tanti), potrebbe evitarmi la fatica di fare il lavoro manualmente ? anche perchè poi, una volta fatto, ad ogni modifica dell'elenco dovrei ripetere manualmente l'operazione
Grazie mille
draleo

[issdr]

pic omnic wrote:

> Nelle celle della colonna J ,ogni tanto, compare il valore : Anno
> …. (cioè i primi 4 caratteri dei dati presenti corrispondono ad
> Anno). Ogni volta che si incontra la parola “Anno”, mi servirebbe
> inserire tante righe vuote, quante ne servono per fare in modo che
> questa riga contenente la parola anno , diventi la prima riga del
> gruppo successivo, cioè vada a finire nella riga multiplo di 7 (+1)
> successiva

sto assumendo che l'ultima cella della colonna J che incrocia l'ultima
riga popolata sia sempre non vuota (ci deve essere Anno... o altro):

--8<---------------cut here---------------start------------->8---
Sub inserisci()
Set c = [J2]
While c.Address <> Range("J" & [J:J].Rows.Count).End(xlUp).Address
If c Like "Anno*" Then
i = IIf((c.Row - 1) Mod 7 <> 0, 8 - (c.Row Mod 7), 0)
For j = 1 To i
c.EntireRow.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Next j
End If
Set c = c(2)
Wend
End Sub
--8<---------------cut here---------------end--------------->8---

[pic omnic]

Molto bene. Funziona benissimo. Magari, ogni tanto, si trovano gruppi di 7 righe totalmente vuoti. Credo dipenda dal fatto che quando la stringa "Anno..." si trova già nella posizione giusta , la procedura inserisce ugualmente il num di righe vuote necessarie a spostarle al gruppo successivo (mentre in tali casi non dovrebbe fare nulla). Ma è una cosa minima e , questi gruppi di righe totalmente vuote,si possono eliminare manualmente. Grazie mille. Mi hai risparmiato tante ore di lavoro...
draleo

[issdr]

pic omnic wrote:

> Credo dipenda dal fatto che quando la stringa "Anno..." si trova già
> nella posizione giusta , la procedura inserisce ugualmente il num di
> righe vuote necessarie a spostarle al gruppo successivo (mentre in
> tali casi non dovrebbe fare nulla)

piuttosto gli "Anno..." che si *trovavano* nella posizione giusta. la
macro faceva già un controllo, il problema è che l'inserimento delle
righe sposta giù anche gli "Anno..." successivi, inclusi quelli che
hanno sopra il numero corretto di righe. probabilmente si può far
meglio, ma per adesso ho inserito un controllo che elimina le righe in
eccesso, guarda se va bene sulla copia del tuo foglio di produzione:

--8<---------------cut here---------------start------------->8---
Sub inserisci()
Set c = [J2]
While c.Address <> Range("J" & [J:J].Rows.Count).End(xlUp).Address
If c Like "Anno*" Then
i = IIf((c.Row - 1) Mod 7 <> 0, 8 - (c.Row Mod 7), 0)
For j = 1 To i
c.EntireRow.Insert Shift:=xlDown

Next j
If c.Row > 8 Then
If Not c(-6) Like "Anno*" Then
Range(c(0).EntireRow, c(-6).EntireRow).Delete
End If
End If

[pic omnic]

Così è perfetta. C'erano circa 400 righe vuote (non sembravano tante !), che ora sono state eliminate. Grazie ancora
draleo
Ps: mi è sembrato di capire che la procedura funziona solo se , in partenza ,prima di lanciare la procedura, tutte le celle della col J contengono qualcosa (come in effetti è adesso). Quindi se volessi inserire, per vari motivi, delle righe vuote, devo ricordarmi che in colonna J devono contenere comunque qualcosa ?

[issdr]

pic omnic wrote:

> Ps: mi è sembrato di capire che la procedura funziona solo se , in
> partenza ,prima di lanciare la procedura, tutte le celle della col J
> contengono qualcosa (come in effetti è adesso). Quindi se volessi
> inserire, per vari motivi, delle righe vuote, devo ricordarmi che in
> colonna J devono contenere comunque qualcosa ?

no, non tutte. deve contenere qualcosa l'ultima cella della J che fa
parte dell'ultima riga con dati; questo per capire dove deve fermarsi la
macro, senza scandagliare tutta la colonna

[issdr]

pic omnic wrote:

> Quindi se volessi inserire, per vari motivi, delle righe vuote, devo
> ricordarmi che in colonna J devono contenere comunque qualcosa ?

sono restio ad utilizzare la proprietà UsedRange, manin questo caso
penso funzioni senza causare noie. sostituisci la riga del While con
queste due e non avrai nemmeno il vincolo dell'ultima cella "piena":

--8<---------------cut here---------------start------------->8---
Set ws = ActiveSheet
While c.Address <> Cells(ws.UsedRange.Rows.Count, "J").Address
--8<---------------cut here---------------end--------------->8---

[pic omnic]

In queste ultime 2 versioni, c' è qualcosa che non va. Non me ne ero accorto prima, ma su un file di prova, ho notato che il num di righe cala da 4100 a 3700 circa e questo non è normale. mentre la prima versione funziona bene (inserisce ogni tanto gruppi di 7 righe, totalmente vuote, ma è un problema minimo). Cercherò domani di eseguire queste ultime 2 procedure ,passo passo, per cercare di capire dove e perchè vengono eliminate così tante righe. Ti farò sapere
draleo

[issdr]

pic omnic wrote:

> In queste ultime 2 versioni, c' è qualcosa che non va. Non me ne ero
> accorto prima, ma su un file di prova, ho notato che il num di righe
> cala da 4100 a 3700 circa e questo non è normale.

hai il sospetto stia cancellando righe "buone"? credo possa accadere
solo nel caso in cui esistano diatanze tra un "Anno..." e l'altro
superiori alle sette celle, scenario che non ho considerato basandomi
sulla tua descrizione.

> mentre la prima versione funziona bene (inserisce ogni tanto gruppi di
> 7 righe, totalmente vuote, ma è un problema minimo). Cercherò domani
> di eseguire queste ultime 2 procedure ,passo passo, per cercare di
> capire dove e perchè vengono eliminate così tante righe. Ti farò
> sapere

se non è complicato, mandami pure un file fantoccio su cui anch'io possa
fare dei test

[pic omnic]

Si, è così. Le ultime 2 procedure Vanno bene perchè portano le righe contente la stringa "Anno..." della colonna J, nella posizione giusta (e questo mi aveva indotto a dire tutto bene). Ma poi, controllando bene la sequenza delle righe, mi sono accorto che cancella anche righe piene (che non dovrebbero essere cancellate) e , guarda caso, sono sempre quelle poste direttamente sopra alla stringa "anno.." In totale saranno 300-350 righe cancellate erroneamente. Domani, se non risolvo da solo, cercherò di inviarti un file fantoccio. Grazie
draleo

[pic omnic]

Credo (spero) di aver capito il problema: quando si inseriscono nuove righe, occorre scansionare le righe partendo dall’alto verso il basso (come nella prima procedura). Ma quando si eliminano righe in eccesso occorre scansionarle partendo dal basso verso l’alto (altrimenti gli indici di riga vengono alterati e si eliminano righe che non dovrebbero essere toccate).
La tua seconda e terza procedura invece cercano di eliminare righe sempre partendo dall’alto e questo porta risultati non corretti. A mio modesto parere occorrerebbe fare 2 distinte procedure da lanciare una dopo l’altra:
1) per inserire righe (la prima procedura va bene)
2) per eliminare i gruppi di righe in eccesso (ma partendo dal basso)
Ti ricordo che vanno eliminati i gruppi INTERI di 7 righe, (eventualmente presenti) e solo quando si verificano le seguenti 2 condizioni:
1) sono tutte vuote nella colonna J.
2) E solo se i vuoti sono dalla posizione 1 alla posizione 7, per ciascun gruppo. (se cioè ci fossero 7 righe vuote dalla posizione 3 alla 10, queste righe vuote vanno mantenute
Al link che segue allego un file di esempio. Nel foglio1 ci sono i dati originali (da non toccare per fare le dovute comparazioni); invece nel foglio2 ci sono i dati da lavorare. Per comodità di lettura ho diviso, con una linea rossa, queste righe in tanti gruppi, di sette righe ciascuno (e la relativa procedura è una vecchia routine di Casanmaner). Ci sono 2 pulsanti ognuno dei quali esegue le tue procedure, seguite da quella di Casanmaner, che ripeto, serve solo per evidenziare i gruppi
Vedi tu se puoi trovare la soluzione
Il link è il seguente
https://www.dropbox.com/scl/fi/m225l6zdyl5cc19y8sjo7/aggiungi_elimina-righe.xlsm?dl=0&rlkey=1nz9wt7lkae1yfqxnw3ot7pct
draleo

[issdr]

pic omnic wrote:

> A mio modesto parere occorrerebbe fare 2 distinte procedure da
> lanciare una dopo l’altra: 1) per inserire righe (la prima procedura
> va bene) 2) per eliminare i gruppi di righe in eccesso (ma partendo
> dal basso) Ti ricordo che vanno eliminati i gruppi INTERI di 7 righe,
> (eventualmente presenti) e solo quando si verificano le seguenti 2
> condizioni: 1) sono tutte vuote nella colonna J. 2) E solo se i vuoti
> sono dalla posizione 1 alla posizione 7, per ciascun gruppo. (se cioè
> ci fossero 7 righe vuote dalla posizione 3 alla 10, queste righe vuote
> vanno mantenute Al link che segue allego un file di esempio.

ho semplificato: trovato "Anno..." elimino tutti gli eventuali vuoti e
parto con l'algoritmo di inserimento righe vuote, che aveva sin
dall'inizio un errore *matematico*, ora sistemato.

per essere sicuro di non cancellare nulla, ho messo in colonna O1 questa
formula (matriciale se non si ha office recente):

=SE(J1:J300="";"";1)

e selezionando la colonna vedo la somma sulla barra di stato, questo
totale non deve variare dopo aver lanciato la macro.

in P1 (sempre matriciale se non si ha office recente):

=SE(RESTO(RIF.RIGA(J1:J300);7)=1;1;"")

il codice:

--8<---------------cut here---------------start------------->8---
Sub inserisci_righe_vuote_quarta()
Set c = [J2]
Set ws = ActiveSheet
While c.Row <= Cells(ws.UsedRange.Rows.Count, "J").Row

If c Like "Anno*" Then

While c(0).Row >= 1 And c(0) = ""
c(0).EntireRow.Delete
Wend
i = IIf((c.Row - 1) Mod 7 <> 0, 7 - (c.Row - 1) Mod 7, 0)

For j = 1 To i
c.EntireRow.Insert Shift:=xlDown
Next j

[pic omnic]

Si. Questa va molto bene. Ho controllato 4200 righe , riga per riga (quasi) e non ho trovato nessuna riga mancante. Grazie Ancora
draleo