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

oakd · 20 Décembre 2021

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 :

Code:

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) :

VB:

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 · 20 Décembre 2021

Bonjour @oakd

Pour supprimer lignes et colonnes cachées je te propose

VB:

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)

VB:

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

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

@Phil69970

oakd · 20 Décembre 2021

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

VB:

  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 · 20 Décembre 2021

Re

Essaye cette version :

VB:

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 · 20 Décembre 2021

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 · 20 Décembre 2021

Re

oakd à dit:
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 · 20 Décembre 2021

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 · 20 Décembre 2021

Re

Un fichier serait le bienvenu

@Phil69970

Phil69970 · 20 Décembre 2021

Re

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

Phil69970 à dit:
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 :

Phil69970 à dit:
Un fichier serait le bienvenu

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

VB:

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

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

oakd

XLDnaute Nouveau

Phil69970

XLDnaute Barbatruc

oakd

XLDnaute Nouveau

Phil69970

XLDnaute Barbatruc

oakd

XLDnaute Nouveau

Phil69970

XLDnaute Barbatruc

Pièces jointes

oakd

XLDnaute Nouveau

Phil69970

XLDnaute Barbatruc

Phil69970

XLDnaute Barbatruc

Spécifications et limites relatives à Excel - Support Microsoft

Pièces jointes

Discussions similaires