Microsoft 365 VBA : extraire chaque feuille puis supprimer toutes les cellules masquées

[oakd]

Bonjour,

Je cherche à extraire séparément toutes les feuilles d'un document excel puis de supprimer toutes les cellules masquées de chacune des feuilles extraites avant de diffuser les fichiers.

J'ai trouvé 2 codes VBA indépendant :

Le code pour extraire chaque feuille séparément :

Sub saveOnglet()
Dim ws
Dim newWk As Workbook

    For Each ws In Worksheets
        Set newWk = Workbooks.Add(xlWBATWorksheet)
        Application.DisplayAlerts = False
        ws.Copy newWk.Sheets(1)
        Sheets("Feuil1").Delete
        newWk.SaveAs (ws.Name & ".xlsx")
        newWk.Close
        Application.DisplayAlerts = True
        Set newWk = Nothing
    Next ws

End Sub

Le code pour supprimer les lignes masquées (cela ne supprime pas les colonnes masquées) :

Sub deleteAllHidden()
    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
        Dim lastRow
        lastRow = 4000
        For iCntr = lastRow To 1 Step -1
            If Rows(iCntr).Hidden = True Then Rows(iCntr).EntireRow.Delete
        Next
    Next ws
End Sub

Est-ce que quelqu'un est capable de fusionner ses 2 codes et d'ajouter la fonction de suppression des colonnes masquées ?

Merci d'avance

 

[Phil69970]

Bonjour @oakd

Pour supprimer lignes et colonnes cachées je te propose

Sub Delete_Ligne_Colonne_Hidden()
Dim Derlig&, DernCol%, i&
Dim Ws As Worksheet
For Each Ws In ActiveWorkbook.Worksheets
    Derlig = Ws.Range("A" & Rows.Count).End(xlUp).Row  'A adapter éventuellement
    DernCol = Split(Ws.Range("A" & Derlig).CurrentRegion.Address, "$")(3) 'A adapter éventuellement
    For i = DernCol To 1 Step -1
        If Columns(i).EntireColumn.Hidden = True Then Columns(i).EntireColumn.Delete Else
    Next
    For i = Derlig To 1 Step -1
        If Rows(i).EntireRow.Hidden = True Then Rows(i).EntireRow.Delete Else
    Next
Next Ws
End Sub

et j'appelle la 2eme macro ici avant de fermer (non tester)

newWk.SaveAs (ws.Name & ".xlsx")
Delete_Ligne_Colonne_Hidden     
newWk.Close

*Un fichier sera utile......pour tester
*Merci de ton retour

@Phil69970

 

[oakd]

Il y a un message d'erreur : Erreur d'exécution "13": Incompatibilité de type
visiblement située sur la ligne :

  DernCol = Split(Ws.Range("A" & Derlig).CurrentRegion.Address, "$")(3) 'A adapter éventuellement

Est-ce possible de créer une seule macro qui exporte chaque feuille séparément puis supprime toutes les cellules masquées uniquement dans les feuilles exportées ? Le fichier source doit rester intact.

 

[Phil69970]

Re

Essaye cette version :

Sub Delete_Ligne_Colonne_Hidden()
Dim Derlig&, DernCol%, i&
Dim Ws As Worksheet
For Each Ws In ActiveWorkbook.Worksheets
    Derlig = Ws.Range("A" & Rows.Count).End(xlUp).Row  'A adapter éventuellement
    DernCol = Range("IV1").End(xlToLeft).Column
    For i = DernCol To 1 Step -1
        If Columns(i).EntireColumn.Hidden = True Then Columns(i).EntireColumn.Delete Else
    Next
    For i = Derlig To 1 Step -1
        If Rows(i).EntireRow.Hidden = True Then Rows(i).EntireRow.Delete Else
    Next
Next Ws
End Sub

*Tu exportes un fichier xlsx qui par définition n'a pas de macro......

@Phil69970

 

[oakd]

Il faut exécuter la macro dans chaque feuille pour qu'il y ait un résultat : les lignes masquées sont bien supprimées, par contre il n'y a que 2 colonnes masquées qui sont supprimées.

 

[Phil69970]

Re

par contre il n'y a que 2 colonnes masquées qui sont supprimées.

Tu en es sur ?

Car la même macro sur ce fichier fonctionne !!!
Sauf si tu as des filtres alors il faut les supprimer avant

@Phil69970

 

[oakd]

Dans une feuille j'ai par exemple la colonne A masquée, la colonne J masquée, les lignes 90 et 91 masquées, il n'y a aucun filtre ni rien d'autres. Lors de l'utilisation de la macro seule la colonne A est supprimée, les autres cellules masquées restent intactes.

 

[Phil69970]

Re

Un fichier serait le bienvenu

@Phil69970

 

[Phil69970]

Re

Il me semble que j'ai écrit dans la macro:

Derlig = Ws.Range("A" & Rows.Count).End(xlUp).Row 'A adapter éventuellement

==> 'A adapter éventuellement
donc tu remplaces par la lettre de la colonne ou tes données peuvent être lues la colonne B ou C ou .... par exemple

Sinon :

Un fichier serait le bienvenu

ou alors on se sert des limites d'excel mais le PC va ramer

Sub Delete_Ligne_Colonne_Hidden()
Application.ScreenUpdating = False
Dim i&
Dim Ws As Worksheet
For Each Ws In ActiveWorkbook.Worksheets
'on se sert des limites maxi d'exel mais le PC va ramer dur !
    For i = 16384 To 1 Step -1
        If Columns(i).EntireColumn.Hidden = True Then Columns(i).EntireColumn.Delete Else
    Next
    For i = 1048576 To 1 Step -1
        If Rows(i).EntireRow.Hidden = True Then Rows(i).EntireRow.Delete Else
    Next
Next Ws
Application.ScreenUpdating = True
End Sub

Spécifications et limites relatives à Excel - Support Microsoft

Dans Excel 2010, la taille maximale des feuilles de calcul est de 1 048 576 lignes par 16 384 colonnes. Cet article décrit les limites et spécifications des fonctionnalités, des classeurs et des feuilles de calcul.

support.microsoft.com

@Phil69970