Condividi:        

Macro nasconde righe in base a valore cella

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

Macro nasconde righe in base a valore cella

Postdi xilofono » 27/11/17 10:14

Buongiorno
ebbene sì per il terrore dei mod ecco l'ennesimo neoiscritto che chiede aiuto, a mia discolpa però vi dico che da anni questo forum è fonte di soluzioni che ho trovato semplicemente leggendo, imparando ed eventualmente adattando, come giusto che sia, il mio problema è che stavolta non mi basta una formula per quanto complessa e di vba non so niente, purtoppo, gli ultimi giorni ho provato e riprovato, leggendo, copiando, cercando di modificare e mi sono arreso.

Ho trovato qui e altrove alcune macro che potenzialmente mi davano soluzione, anche se non soddisfacevano proprio ogni cosa,, alcune erano istantanee, altre impiegavano 1 minuto per applicarsi, in ognic caso a causa della mia ignoranza non ho saputo adattarle.

Di per sè sarebbe semplice, una macro che nasconde una riga (applicata alle righe dalla 15 alla 2800) se il valore della colonna (U) corrispondente è pari a 1. Le riscoprisse quando il valore non fosse 1.

Il file su cui opero contiene già filtri automatici, lavora su oltre 2000 righe, ma mi serve un ulteriore filtro che non posso ottenere nè con quello automatico nè con quello avanzato.
Le condizioni per attivare la macro in realtà sarebbero state più complesse, ma per semplificarla mi sono appoggiato su una formula che mi restituisce 1 nella colonna U, quindi la semplicità è solo apparente.

La macro dovrebbe funzionare solo sul foglio di riferimento (in tal caso foglio1), partire quando il valore della cella G3 o di quella G6 varia (trattasi di menu a discesa), inoltre l foglio è protetto e servirebbe che la macro togliesse la protezione, si chiamasse, e poi rimettesse la protezione (non c'è password).

Il file in teoria non posso allegarlo in quanto contiene dati personali di due migliaia e rotti di utenti del gas di una città italiana, se necessario cancello qualche dato e allego, ma credo di aver spiegato le condizioni, in ogni caso le posso riassumere in forma sintetica:

-foglio1
-si attiva al variare delle celle G3 o G6
-la macro toglie la protezione
-la macro si applica alle righe 15:2800
-la macro nasconde una di queste righe se nella colonna u una formula restituisce il valore 1, scopre quelle che non restituiscono 1
-la macro rimette la protezione

Chiedendovi scusa per il disturbo e sperando in una mano
un saluto
Alessandro
Windows 11 - Office 2016 Ita
Avatar utente
xilofono
Utente Junior
 
Post: 80
Iscritto il: 27/11/17 09:52

Sponsor
 

Re: Macro nasconde righe in base a valore cella

Postdi xilofono » 27/11/17 10:42

ecco una immagina della struttura del foglio

http://oi64.tinypic.com/255m6pi.jpg
Windows 11 - Office 2016 Ita
Avatar utente
xilofono
Utente Junior
 
Post: 80
Iscritto il: 27/11/17 09:52

Re: Macro nasconde righe in base a valore cella

Postdi alfrimpa » 27/11/17 11:38

Ciao Alessandro

Allegare un'immagine non serve a nulla perché se pure si scrive la macro non abbiamo il file su cui poterla provare.

Allegalo quindi e vediamo.
Alfredo

Win7 + Office 2007
Avatar utente
alfrimpa
Utente Senior
 
Post: 1201
Iscritto il: 30/12/13 17:01
Località: Napoli

Re: Macro nasconde righe in base a valore cella

Postdi xilofono » 27/11/17 11:46

hai ragione, ecco il file, protetto ma senza pass

http://www.filedropper.com/resocontocamacro
Windows 11 - Office 2016 Ita
Avatar utente
xilofono
Utente Junior
 
Post: 80
Iscritto il: 27/11/17 09:52

Re: Macro nasconde righe in base a valore cella

Postdi alfrimpa » 27/11/17 12:55

Scusa Alessandro una domanda stupida ma perché non usi un filtro sulla colonna U?
Alfredo

Win7 + Office 2007
Avatar utente
alfrimpa
Utente Senior
 
Post: 1201
Iscritto il: 30/12/13 17:01
Località: Napoli

Re: Macro nasconde righe in base a valore cella

Postdi xilofono » 27/11/17 13:03

alfrimpa ha scritto:Scusa Alessandro una domanda stupida ma perché non usi un filtro sulla colonna U?


figurati
perchè in pratica la visualizzazione delle righe tra 15 e 2800 deve essere abbinata al calcolo - già presente - nelle prime 14 righe

il calcolo delle prime 14 infatti varia in base alla combinazione dei due menù a tendina, prendendo in considerazione solo quelle che corrispondono ai criteri, questo è stato possibile farlo tramite una semplice formula combinata di conta.più.se

in contemporanea anche le righe visualizzate dovrebbero diventare solo quelle che vengono contate al variare dei due menu a discesa, ma questo può farlo solo la macro

usando il filtro automatico si dovrebbe prima intervenire sui menu a tendina, poi successivamente sul filtro automatico, o viceversa, con la macro richiesta, invece è tutto sincronizzato, il foglio rende sia il calcolo come già fa, e restringerebbe anche la visualizzazione delle righe a quelle che determinano il calcolo

purtroppo il filtro automatico non è dinamico, se io setto di farmi vedere solo dove U è diverso da 1, e poi i valori di U cambiano, il filtro non si aggiorna automaticamente, cioè dovrei attivarlo ogni volta che cambiano i valori, dunque fare due operazioni comunque, prima sui menu, poi sul filtro, che è ciò che la macro dovrebbe evitare
Windows 11 - Office 2016 Ita
Avatar utente
xilofono
Utente Junior
 
Post: 80
Iscritto il: 27/11/17 09:52

Re: Macro nasconde righe in base a valore cella

Postdi alfrimpa » 27/11/17 14:37

Prova questa macro da inserire nel modulo del foglio interessato.

Codice: Seleziona tutto
Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Long
Dim UR As Long
UR = Cells(Rows.Count, "U").End(xlUp).Row
If Not Intersect(Target, Range("G3, G6")) Is Nothing Then
Application.EnableEvents = False
ActiveSheet.Unprotect
For i = UR To 15 Step -1
    If Range("U" & i).Value = 1 Then
        Range("U" & i).Rows.Hidden = True
        Else
        Range("U" & i).Rows.Hidden = False
    End If
Next i
ActiveSheet.Protect
Application.EnableEvents = True
End If
End Sub


Due precisazioni:

1) dal tuo foglio ho eliminato la struttura tabella (per motivi che sarebbe un po' lungo spiegare)
2) la macro è piuttosto lenta (forse per l'alto numero di record da elaborare)
Alfredo

Win7 + Office 2007
Avatar utente
alfrimpa
Utente Senior
 
Post: 1201
Iscritto il: 30/12/13 17:01
Località: Napoli

Re: Macro nasconde righe in base a valore cella

Postdi xilofono » 27/11/17 14:50

Ciao alfredo ti ringrazio,
anche se ho qualche problema nell'implementarla, nel senso che, dopo aver tolto la struttura tabella (che ahimè in realtà servirebbe per filtrare ulteriormente), inserisco il codice che mi hai fornito salvo ma non succede niente.
fino ad ora i codici che avevo provato li avevo implementati così e qualcosa "muovevano".
Windows 11 - Office 2016 Ita
Avatar utente
xilofono
Utente Junior
 
Post: 80
Iscritto il: 27/11/17 09:52

Re: Macro nasconde righe in base a valore cella

Postdi xilofono » 27/11/17 15:43

ok errore mio, ora con più calma l'ho attivata, in effetti è un po' lenta rispetto ad esempio a dei tentativi che avevi fatto con questa che risultava immediata ma non di adattava perfettamente

viewtopic.php?t=108990#p639665

però il problema è che sì nasconde, ma non riscopre quando il valore della colonna U ricambia
nella colonna U i valori possono variare di continuo e passare da campo vuoto a 1 e viceversa
Ultima modifica di xilofono su 27/11/17 15:45, modificato 1 volte in totale.
Windows 11 - Office 2016 Ita
Avatar utente
xilofono
Utente Junior
 
Post: 80
Iscritto il: 27/11/17 09:52

Re: Macro nasconde righe in base a valore cella

Postdi alfrimpa » 27/11/17 15:44

Allega il file non funzionante.

Hai inserito il codice nel modulo del foglio e non in uno standard?

Perché, di solito, è questo l'errore.
Alfredo

Win7 + Office 2007
Avatar utente
alfrimpa
Utente Senior
 
Post: 1201
Iscritto il: 30/12/13 17:01
Località: Napoli

Re: Macro nasconde righe in base a valore cella

Postdi xilofono » 27/11/17 15:49

scusami poi ho risolto per quello, errore banale mio, è che non sono aduso ad usare macro, l'unico problema è che nasconde ma non scopre quando il valore nella colonna U non è più 1
Windows 11 - Office 2016 Ita
Avatar utente
xilofono
Utente Junior
 
Post: 80
Iscritto il: 27/11/17 09:52

Re: Macro nasconde righe in base a valore cella

Postdi alfrimpa » 27/11/17 16:06

Nell'esempio che hai allegato tutte le formule in colonna U restituiscono 1 come risultato.

Prova a fare qualche esempio in cui la formula restituisca un valore diverso da 1
Alfredo

Win7 + Office 2007
Avatar utente
alfrimpa
Utente Senior
 
Post: 1201
Iscritto il: 30/12/13 17:01
Località: Napoli

Re: Macro nasconde righe in base a valore cella

Postdi xilofono » 27/11/17 16:12

alfrimpa ha scritto:Nell'esempio che hai allegato tutte le formule in colonna U restituiscono 1 come risultato.

Prova a fare qualche esempio in cui la formula restituisca un valore diverso da 1


possono restituire o 1 o campo vuoto, lo assicuro :D
nell'esempio allegato - di partenza - le righe da 439 a 441 sono con campo vuote, tutte le altre rendono 1 nella colonna U

ad esempio: se io setto g3 in "fonsarda" e g6 in "del platano", le righe da 650 a 655 alla colonna U restituiranno campo vuoto, tutte le altre 1 (da nascondere)

se - ad esempio - successivamente setto g6 in "galassi", le righe con campo vuoto in colonna U diventeranno quelle da 656 a 690, tutte le altre renderanno 1

similmente al variare di g3 e g6 la colonna U può restituire 1 e campo vuoto nelle più differenti posizioni (i campi vuoti saranno però sempre contigui, senza salti), e in tal caso le celle dovrebbero nascondersi (=1) e scoprirsi di conseguenza

gli estremi sono che siano tutte uguali a campo vuoto (nel caso i g3 e g6 siano settati su "tutto" entrambi) duqnue tutte da scoprire, oppure che siano tutte con valore 1, dunque tutte da nascondere (questo avviene nei casi di incoerenza tra i due menu a discesa concatenati con formula indiretto g3 e g6, in tal caso i caratteri di queste due celle diventano rossi a segnalare l'incoerenza a chi ne fa uso)
Windows 11 - Office 2016 Ita
Avatar utente
xilofono
Utente Junior
 
Post: 80
Iscritto il: 27/11/17 09:52

Re: Macro nasconde righe in base a valore cella

Postdi alfrimpa » 27/11/17 16:27

Così sembrerebbe funzionare

Codice: Seleziona tutto
Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Long
Dim UR As Long
UR = Cells(Rows.Count, "U").End(xlUp).Row
If Not Intersect(Target, Range("G3, G6")) Is Nothing Then
Application.EnableEvents = False
ActiveSheet.Unprotect
For i = UR To 15 Step -1
    If Range("U" & i).Value = 1 Then
        Range("U" & i).Rows.Hidden = True
        ElseIf Range("U" & i).Value = "" Then
       
        Range("U" & i).Rows.Hidden = False
    End If
Next i
ActiveSheet.Protect
Application.EnableEvents = True
End If
End Sub


Ma rimangono comunque i tempi di attesa per me inaccettabili.

Magari attendi qualche intervento migliorativo di qualcun altro.
Alfredo

Win7 + Office 2007
Avatar utente
alfrimpa
Utente Senior
 
Post: 1201
Iscritto il: 30/12/13 17:01
Località: Napoli

Re: Macro nasconde righe in base a valore cella

Postdi xilofono » 27/11/17 16:59

non scopre ancora le celle che passano da 1 a campo vuoto, comunque ti ringrazio perchè già solo comparando ciò che hai aggiunto un po' riesco a entrare dentro il linguaggio
si in effetti i tempi vanno da 8 a 25 secondi, io stavo pensando di usare un codice come questo, che mi richiederebbe molto più lavorio ma è immediato, purtroppo se gli intervalli si intersecano non funziona più dunque non va, l'avevo già provata del resto

Codice: Seleziona tutto
Private Sub Worksheet_Change(ByVal Target As Range)
Dim arRange, aFrasi
'
If Target.Address = "$B$10" Then
    aFrasi = Array("prima", "seconda", "terza frase", "Quarta frase")   '<<< Le frasi
    arRange = Array("20:22", "25:26", "18:19,23:23", "28:29,33:34")     '<<< Le righe che nascondono
    For I = 0 To UBound(arRange)
        If InStr(1, Target.Value, aFrasi(I), vbTextCompare) > 0 Then
            Rows(arRange(I)).Hidden = True
        Else
            Range(arRange(I)).EntireRow.Hidden = False
        End If
    Next I
End If
End Sub

trovata qui viewtopic.php?t=108990#p639665


uff non pensavo fosse così difficile
se solo excel mi desse l'opportunità di basare il filtro della tabella sul valore di una cella esterna, che può variare di continuo, risolverei tutto, perchè di fatto di quello si tratta attraverso tanti giri, ma non si può :D dunque devo creare questo filtro vba sopra i filtri della tabella che pure servono...
Windows 11 - Office 2016 Ita
Avatar utente
xilofono
Utente Junior
 
Post: 80
Iscritto il: 27/11/17 09:52

Re: Macro nasconde righe in base a valore cella

Postdi xilofono » 27/11/17 17:45

forse ho trovato una soluzione, più semplice ma di cui ignoro completamente la fattibilità, non ho mai letto una cosa del genere:

una macro che, al variare delle celle g3 e g6, toglie la protezione, riapplica automaticamente i filtri nel foglio, rimette la protezione

in questo modo lasciando un filtro fisso sulla colonna U (visualizza i risultati diversi da 1), man mano che la colonna U presenta mutamenti nei valori (il che avviene quando si agisce sulle celle g3 e g6), il filtro si riaggiorna da solo nascondendo i valori 1 e scoprendo gli altri
Windows 11 - Office 2016 Ita
Avatar utente
xilofono
Utente Junior
 
Post: 80
Iscritto il: 27/11/17 09:52

Re: Macro nasconde righe in base a valore cella

Postdi Anthony47 » 27/11/17 19:53

Per usare la macro di Alfredo (la prima) bisogna modificare il calcolo di UR (Ultima Riga), mentre una piccola velocizzazione si potrebbe ottenere inserendo Application.ScreenUpdating = False /True. Ad esempio
Codice: Seleziona tutto
Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Long
Dim UR As Long
'mytim = Timer
'UR = Cells(Rows.Count, "U").End(xlUp).Row
UR = Range("B14").ListObject.DataBodyRange.Rows.Count + 14     'Nota1
If Not Intersect(Target, Range("G3, G6")) Is Nothing Then
Application.EnableEvents = False
Application.ScreenUpdating = False
ActiveSheet.Unprotect
For i = UR To 15 Step -1
    If Range("U" & i).Value = 1 Then
            Range("U" & i).Rows.Hidden = True
        Else
            Range("U" & i).Rows.Hidden = False
    End If
Next i
ActiveSheet.Protect
Application.EnableEvents = True
Application.ScreenUpdating = True
'MsgBox (Format(Timer - mytim, "0.00"))
End If
End Sub
Nota1: per calcolo di UR conteggio il numero di righe presenti nella tabella cui appartiene B14

Una ulteriore velocizzazione si potrebbe poi ottenere modificando l'algoritmo di Nascondi /Scopri, nella parte interna del codice:
Codice: Seleziona tutto
ActiveSheet.Unprotect
Rows("15:" & UR).Hidden = False                    '+++
For i = UR To 15 Step -1
    If Range("U" & i).Value = 1 Then
            Range("U" & i).Rows.Hidden = True
        Else
'            Range("U" & i).Rows.Hidden = False    '---
    End If
Next i
'ActiveSheet.Protect

Le istruzioni toccate sono marcate +++ e ---; il miglioramento e' evidente solo quando si passa da molte a poche o niente celle nascoste.

Ma l'unico miglioramento veramente efficace si puo' ottenere solo lavorando sul filtro della colonna U:
Codice: Seleziona tutto
Private Sub Worksheet_Change(ByVal Target As Range)
'Filtro su colonna U
Dim i As Long
Dim UR As Long
'mytim = Timer
'
If Not Intersect(Target, Range("G3, G6")) Is Nothing Then
    UR = Range("B14").ListObject.DataBodyRange.Rows.Count + 14
    Application.EnableEvents = False
    ActiveSheet.Unprotect
        ActiveSheet.Range("$U$14:$U$" & UR).AutoFilter Field:=1, Criteria1:="="
    ActiveSheet.Protect
    Application.EnableEvents = True
'    MsgBox (Format(Timer - mytim, "0.00"))
End If
End Sub

Ciao
Avatar utente
Anthony47
Moderatore
 
Post: 19196
Iscritto il: 21/03/06 16:03
Località: Ivrea

Re: Macro nasconde righe in base a valore cella

Postdi xilofono » 27/11/17 21:06

anthony ti ringrazio, questo codice è veloce e filtra perfettamente, che altro posso dire se non perfetto!

un unica domanda che posso porre è, visto che sicuramente mi sarà utile anche in altre circostanze, in che modo adattarlo - utilizzandolo ovviamente nella stessa logica - ad altri contesti, nel senso, variare la colonna di riferimento o il valore che fa nascondere la riga
vorrei imparare ad adattarlo, in modo da imparare a pescare piuttosto che avere un pesce, è una base perfetta-

non posso che ringraziarti ancora!
Windows 11 - Office 2016 Ita
Avatar utente
xilofono
Utente Junior
 
Post: 80
Iscritto il: 27/11/17 09:52

Re: Macro nasconde righe in base a valore cella

Postdi Anthony47 » 28/11/17 00:08

La macro intercetta l'evento "Worksheet Change" ("Worksheet.Change").
Controlla che esso sia scattato su una modifica delle celle G3 o G6 ("Application.Intersect"); se Si, allora applica il filtro automatico ("Range.AutoFilter") sulla colonna U per le righe della tabella ("ListObject.DataBodyRange")
Application.EnableEvents = False /True serve per evitare ulteriori "eventi" durante la gestione di questo evento.
Le voci tra parentesi corrispondono alla ricerca da fare nell'help on line del vba per ottenere informazioni sulle vare istruzioni. In generale se posizioni il cursore (con 1 click) dentro una parola chiave del codice e poi premi F1 puoi attivare l'help on line contestuale, cioe' (in genere) con il significato di quella parola usata in quel contesto, insieme con la sintassi e (in genere) utili esempi.

Altro sistema per imparare e' procedere autoregistrando le macro; per le istruzioni e i primi suggerimenti su come registrare una macro:
viewtopic.php?f=26&t=103893&p=622593#p622593

Ciao
Avatar utente
Anthony47
Moderatore
 
Post: 19196
Iscritto il: 21/03/06 16:03
Località: Ivrea

Re: Macro nasconde righe in base a valore cella

Postdi xilofono » 28/11/17 14:07

ora registrando qualche azione sto prendendo confidenza e ho potuto apportare qualche modifica che gira perfettamente sulla tua base, ti ringrazio di nuovo!
Windows 11 - Office 2016 Ita
Avatar utente
xilofono
Utente Junior
 
Post: 80
Iscritto il: 27/11/17 09:52

Prossimo

Torna a Applicazioni Office Windows


Topic correlati a "Macro nasconde righe in base a valore cella":

BTp Valore
Autore: MarioLombardi
Forum: Forum off-topic
Risposte: 2

Chi c’è in linea

Visitano il forum: Marius44 e 48 ospiti