Nascondere le colonne con celle vuote dopo aver applicato il filtro automatico alle righe.

Question

Vorrei sapere se è possibile applicare un filtro alle colonne in modo da visualizzare solo quelle che contengono dati, dopo aver applicato un filtro automatico sui dati presenti nelle righe.

Grazie

Answer 1

Ciao Lblu,

Complimenti Norman! E’ eccezionale! E poi sei stato dettagliato nella spiegazione.

Ho notato che, pur inserendo altre colonne a destra o altre righe in basso, il tutto continua a funzionare, senza effettuare variazioni sui nomi di range o altro.

Prego!

Ho, comunque, bisogno di qualche correzione, anche perché ho inserito delle colonne a sinistra e dei gruppi:

• Quando aggiorno i dati della matrice, per ogni valore inserito/cancellato nelle celle, si attiva la macro che nasconde le colonne e sono costretto a premere il pulsante che mostra le colonne dopo ogni dato inserito.

OK - credo che sia superabile!

• Si può prevedere che all’apertura del file venga mostrato sempre tutto, senza filtri e senza colonne nascoste, e fare in modo che un terzo pulsante All mostri tutto, eliminando filtri e mostrando le colonne nascoste?

Yes!

• Per la colonna Gruppo le celle sono unite e, in caso di filtro per Gruppo, viene considerata solo la prima riga delle Applicazioni. Probabilmente non si riesce perché il dato del Gruppo è solo nella prima cella anche se la visualizzazione comprende le altre celle unite.

DETESTO, con anima e cuore, le celle uniti e io non le utilizzerei mai! Nel caso del tuo file, vorrei suggerire di immettere il nome del gruppo su ogni riga di interesse e poi impostare un colore di testo bianco per nascondere il testo per tutte le righe tranne una.

• E’ possibile selezionare una cella e premere un pulsante che filtra secondo il contenuto della cella?

Credo di  sì. Comunque, ti chiederei gentilmente di fornire una spiegazione più dettagliata della tua esigenza.

• Nella colonna AD21 del tuo file di esempio compare la formula =SOMMA(myFilter) che presenta l’errore #NOME?. E’ una formula che serve? L’ho eliminata e il tutto continua a funzionare.

Quella formula non ha più né rilevanza né qualsiasi utilità!

• Nelle tue spiegazioni dici di inserire un foglio nuovo e immettere la formula =SUBTOTALE(3;FiltroAutomatico). Utilizzando il tuo file di esempio, il menù Home/Formato/Visibilità - Nascondi e scopri/Scopri foglio…, non è selezionabile e non è possibile visualizzare il foglio.

Non vedi il foglio perchè avevo assegnato il valore xlVeryHidden alla proprietà Sheet.Visible e, in questo modo non si può visualizzarlo dalla interfaccia Excel. Potrei invece nascondere il foglio in modo che si possa anche renderlo visibile dalla  interfaccia Excel.

• Nel tuo file di esempio ho inserito un terzo pulsante con una macro che cancella i filtri ma va in errore se non vi sono filtri attivi.

Sub MostraTutto()

'

' MostraTutto Macro

'

    ActiveSheet.ShowAllData

End Sub

Prova invece qualcosa del genere:

'=========>>

Public Sub CancellaFiltri()

    With ActiveSheet

        If .FilterMode Then

            .AutoFilter.ShowAllData

        End If

    End With

End Sub

'<<=========

Ti chiederei gentilmente di caricare un file di esempio.

===

Regards,

Norman

Answer 2

Complimenti Norman! E’ eccezionale! E poi sei stato dettagliato nella spiegazione.

Ho notato che, pur inserendo altre colonne a destra o altre righe in basso, il tutto continua a funzionare, senza effettuare variazioni sui nomi di range o altro.

Ho, comunque, bisogno di qualche correzione, anche perché ho inserito delle colonne a sinistra e dei gruppi:

1. Quando aggiorno i dati della matrice, per ogni valore inserito/cancellato nelle celle, si attiva la macro che nasconde le colonne e sono costretto a premere il pulsante che mostra le colonne dopo ogni dato inserito.
2. Si può prevedere che all’apertura del file venga mostrato sempre tutto, senza filtri e senza colonne nascoste, e fare in modo che un terzo pulsante All mostri tutto, eliminando filtri e mostrando le colonne nascoste?
3. Per la colonna Gruppo le celle sono unite e, in caso di filtro per Gruppo, viene considerata solo la prima riga delle Applicazioni. Probabilmente non si riesce perché il dato del Gruppo è solo nella prima cella anche se la visualizzazione comprende le altre celle unite.
4. E’ possibile selezionare una cella e premere un pulsante che filtra secondo il contenuto della cella?
5. Nella colonna AD21 del tuo file di esempio compare la formula =SOMMA(myFilter) che presenta l’errore #NOME?. E’ una formula che serve? L’ho eliminata e il tutto continua a funzionare.
6. Nelle tue spiegazioni dici di inserire un foglio nuovo e immettere la formula =SUBTOTALE(3;FiltroAutomatico). Utilizzando il tuo file di esempio, il menù Home/Formato/Visibilità - Nascondi e scopri/Scopri foglio…, non è selezionabile e non è possibile visualizzare il foglio.
7. Nel tuo file di esempio ho inserito un terzo pulsante con una macro che cancella i filtri ma va in errore se non vi sono filtri attivi.

Sub MostraTutto()

'

' MostraTutto Macro

'

    ActiveSheet.ShowAllData

End Sub

Intanto ti ringrazio per la soluzione che mi hai fornito.

Saluti

Lblu

Answer 3

Ciao Lblu,

Ho una matrice di questo tipo:

e ho la necessità di filtrare i dati in base alle 2 domande:

Quali sono i servizi che vengono richiamati da un'applicazione?

Quali sono le applicazioni che utilizzano un certo servizio?

Manualmente, filtrando le righe e nascondendo le colonne, riesco ad ottenere quanto mostrato nelle 2 figure seguenti:

Considerando che le applicazioni e i servizi sono tanti, vorrei effettuare l’operazione automaticamente, anche utilizzando un pulsante + VBA.

Ma per il caso potrebbero essere utilizzate anche le Tabelle Pivot o servono per altri scopi?

Bene! Hai spiegato lo scenario in modo esemplare!

Penso che, da solo, ci potrebbe essere dei problemi con l'uso di un pulsante. Considera, ad esempio lo scenario in cui alcune colonne della tabella fossero state nascoste e l'utente decidesse di modificare i criteri di filtraggio di una tabella, già  filtrata, ma trascurasse successivemente di premere il pulsante. In tal caso i dati visualizzati potrebbero non comprendono tutti i dati di interesse.

Tuttavia, credo che si possa superare tali considerazioni, sfuttando un foglio di appoggio, eventualmente nascosto, e tre procedure evento. Questo codice evento agirirà per nascondere/visualizzare automaticamente le colonne della tabella in risposta a qualunque modifica dei  criteri di filtraggio. Per commodità, aggiungerei due pulsante: uno per bypassare il codice e visualzzare tutte le colonne e l'altro per rinasconderle senza modificare i criteri.

Quindi, prova qualcosa del genere:

• Fai clic dx sulla linguetta del foglio con il filtro automatico
• Seleziona l'opzione Visualizza Codice dal menu contestuale risultante
• Incolla il seguente codice:

'=========>>

Option Explicit

'--------->>

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim oName As Name

    If Me.AutoFilterMode Then

        Set RngAutoFilter = Me.AutoFilter.Range

        Set oName = ThisWorkbook.Names.Add( _

                    Name:="FiltroAutomatico", _

                    RefersTo:=RngAutoFilter)

    End If

End Sub

'<<=========

• Alt+Q per tornare a Exce
• Nella cella A1 di un altro foglio, immetto la formula:

        =SUBTOTALE(3;FiltroAutomatico)

• Fai clic dx sulla linguetta di questi altro foglio
• Seleziona l'opzione Visualizza Codicedal menu contestuale risultante
• Incolla il seguente codice:

'=========>>

Option Explicit

'--------->>

Private Sub Worksheet_Calculate()

    Call NascondaColonneVuote

End Sub

'<<=========

• Alt+F11 per aprire l'editor di VBA
• Alt+IM per inserire un nuovo modulo di codice
• Nel nuovo modulo vuoto, incolla il seguente codice:

'=========>>

Option Explicit

Public RngAutoFilter As Range

Public Const sFoglioAutoFilter As String = "Foglio1"           '<<=== Modifica

'--------->>

Public Sub NascondaColonneVuote()

    Dim WB As Workbook

    Dim SH As Worksheet

    Dim RngAutoFilter As Range, RngDati As Range, rngVisible As Range

    Dim rCell As Range, rCol As Range

    Dim bHidden As Boolean

    Set WB = ThisWorkbook

    Set SH = WB.Sheets(sFoglioAutoFilter)

    Set RngAutoFilter = SH.AutoFilter.Range

    If Not RngAutoFilter Is Nothing Then

        On Error GoTo XIT

        Application.ScreenUpdating = False

        With RngAutoFilter

            Set RngDati = .Offset(1, 1).Resize(.Rows.Count - 1, .Columns.Count - 1)

            .EntireColumn.Hidden = False

        End With

        On Error Resume Next

        RngAutoFilter.EntireColumn.Visible = True

        Set rngVisible = RngDati.SpecialCells(xlCellTypeVisible)

        For Each rCol In RngDati.Columns

            For Each rCell In rCol.Cells

                With rCell

                    If .EntireRow.Hidden = False Then

                        bHidden = .Value = vbNullString

                        If Not bHidden Then Exit For

                    End If

                End With

            Next rCell

            rCol.EntireColumn.Hidden = bHidden

        Next rCol

    End If

XIT:

    Application.ScreenUpdating = True

End Sub

'--------->>

Public Sub VisualizzaColonne()

    Dim Rng As Range, rCellaPulsante As Range

    Set rCellaPulsante = ActiveSheet.Buttons(Application.Caller).TopLeftCell

    Set Rng = rCellaPulsante.CurrentRegion

    Rng.EntireColumn.Hidden = False

End Sub

'<<========= 

• Ctrl+R per accedere alla finestra Project Explorer ('Gestione progetti')
• Fai doppio clic sul modulo ThisWorkbook (Questa_cartella_di_Lavoro) del file e incolla il seguente codice:

'=========>>

Option Explicit

'--------->>

Private Sub Workbook_Open()

    Dim SH As Worksheet

    Dim oName As Name

    Set SH = Me.Sheets(sFoglioAutoFilter)

    With SH

        If .AutoFilterMode Then

            Set RngAutoFilter = .AutoFilter.Range

            Set oName = ThisWorkbook.Names.Add( _

                        Name:="FiltroAutomatico", _

                        RefersTo:=RngAutoFilter)

        End If

    End With

End Sub

'<<=========

• Alt+Q per chiudere l'editor di VBA e tornare a Excel
• Dentro i confini della prima cella (A1 ?) del foglio con il filtro, inserisci due pulsante, l'uno con il sego + e l'altro con il segno meno

  

• Fai clic sul primo pulsante
• Assegna macro
• digita VisualizzaColonne
• Fai clic sul secondo pulsante
• Assegna macro
• digita NascondaColonneVuote
• Salva il file con l’estensione xlsm

Potresti scaricare il mio file di prova Lblu20170926.xlsm

Nota che ho nascosto il secondo foglio, Foglio2, nel mio file di prova

===

Regards,

Norman

Answer 4

Salve Norman,

Ho una matrice di questo tipo:

e ho la necessità di filtrare i dati in base alle 2 domande:

Quali sono i servizi che vengono richiamati da un'applicazione?

Quali sono le applicazioni che utilizzano un certo servizio?

Manualmente, filtrando le righe e nascondendo le colonne, riesco ad ottenere quanto mostrato nelle 2 figure seguenti:

Considerando che le applicazioni e i servizi sono tanti, vorrei effettuare l’operazione automaticamente, anche utilizzando un pulsante + VBA.

Ma per il caso potrebbero essere utilizzate anche le Tabelle Pivot o servono per altri scopi?

Grazie

Answer 5

Ciao Lblu,

Vorrei sapere se è possibile applicare un filtro alle colonne in modo da visualizzare solo quelle che contengono dati, dopo aver applicato un filtro automatico sui dati presenti nelle righe.

Non è possibile nativemente filtrare, e quindi escludere, colonne vuote con lo strumento filtro automatico. Credo sia necessario ricorre a VBA.

Tuttavia, prima di suggerire un codice adatto, vorrei chiederti di pubblicare dei screenshot esemplificativi dei dati filtati e non filtrati  per mostrare il genere di risultati voluti. Ancor meglio sarebbe caricare un file di esempio.

===

Regards,

Norman