XL 2021 VBA Alternative à SpecialCells(xlCellTypeVisible)

[Dudu2]

Bonjour

SpecialCells ne fonctionne pas sur une feuille protégée (erreur 1004).
J'ai besoin de connaître le Range des cellules visibles d'une feuille protégée.
Auriez-vous une fonction performante qui fait ça ?

Sub a()
    Dim R As Range
 
    On Error Resume Next
    Set R = ActiveSheet.Cells.SpecialCells(xlCellTypeVisible)
    MsgBox "Err.Number = " & Err.Number & IIf(Err.Number = 0, ", Range = " & R.Address(0, 0), "")
End Sub

 

[bsalv]

je ne comprends pas la question, votre fonction, est-elle plus performante que "specialcells" ? Je ne le crois pas. Supposons qu'on le fait avec 500 lignes au lieu d'une trentaine.

 

[Dudu2]

La seule question facile à comprendre est de trouver un cas qui confirme votre assertion: "A mon avis, aucune de vos fonctions est fiable assez pour remplacer "specialcells"."

Il ne s'agit pas de trouver une fonction plus performante que "specialcells" qui fonctionne sur tout type de Range parce qu'Excel dispose de tables internes que nous n'avons pas.

Ceci dit, selon les cas, la fonction fournie dans le fichier du Post #30 peut être plus ou moins performante que "specialcells". Suffit de tester.
Mais ça n'a aucune importance car le problème (indiqué au Post #1 de ce sujet) est que "SpecialCells ne fonctionne pas sur une feuille protégée (erreur 1004)." Alors il faut trouver une solution alternative qui ne soit pas rédhibitoire en termes de temps de réponse sur les cas "standards" de masquage sur une feuille protégée.

 

[Dudu2]

Cependant, je dois reconnaître que votre idée du Post #17:

Mieux serait de vérifier ceci : if worksheet.cells(rows.count,1).top=0 then exit function

corrigée de "+ .Height", a été salutaire pour permettre de tester la fin d'une zone masquée.

A noter que je n'ai pas écrit la fonction de recherche dychotomique de fin de zone masquée sur ce principe, qui serait absolument généraliste et stable en performance pour tous les cas. Celle fournie en Post #30 est suffisante et je n'ai pas envie de ma casser la tête.

 

[bsalv]

Celle fournie en Post #30 est suffisante et je n'ai pas envie de ma casser la tête.

okay, pour moi, cela suffit aussi ...

 

[mapomme]

Bonsoir à tous ,

Une petite dernière fonction très simple (pas forcément la plus rapide sans doute) qui utilise un classeur auxiliaire:
Le mot de passe de chaque feuille est "toto".

Function RangeVisibleCells(MyWorksheet As Worksheet) As Range
Dim wkb As Workbook, wks As Worksheet, xarea As Range, x As Range, res As Range
    Application.ScreenUpdating = False                ' blocage affichage
    Set wkb = Workbooks.Add: Set wks = wkb.Sheets(1)  ' nouveau classeur
    On Error GoTo Menage                              ' si erreur
    MyWorksheet.Rows.Copy: wks.Rows.PasteSpecial xlPasteFormats         ' copie des lignes de la feuille vers le nouveau classeur
    For Each xarea In wks.Cells.SpecialCells(xlCellTypeVisible).Areas   ' pour chaque plage visible de la nouvelle feuille
      Set x = MyWorksheet.Range(xarea.Address)                          ' x : la plage correspondante de la feuille source
      If res Is Nothing Then Set res = x Else Set res = Union(res, x)   ' on ajoute la plage x à Res
   Next xarea
Menage:
   Set RangeVisibleCells = res      ' on affecte les plages visibles à la fonction
   wkb.Close savechanges:=False     ' on supprime le classeur auxiliaire
End Function
 
Sub TestFeuilleActive()
Dim xrg As Range
   Set xrg = RangeVisibleCells(ActiveSheet)
   If xrg Is Nothing Then MsgBox "Aucune cellule n'est visible", vbExclamation: Exit Sub
   Application.Goto xrg: MsgBox xrg.Address(0, 0), vbInformation
End Sub

 

[Dudu2]

Bon, en fait, bug il y avait ! Sur le UsedRangeOnly:=True, décalage colonne non pris en compte donc correction de la solution et du fichier.

 

[Dudu2]

Bonsoir @mapomme,
Méthode originale, un peu moins performante (1/4 seconde) mais probablement plus constante selon les configurations de masquage.
Et j'ai vérifié, tu sors le même Range qu'Excel.

Par exemple: si on masque la ligne 100.000, ça étend de facto le UsedRange jusqu'à la 100.000ème ligne !
Ma méthode prends alors 0.4 secondes à parcourir toutes ces lignes alors que ta méthode est constante à 0.25 secondes.
Peut-être faudrait-il faire un mix à partir de 100.000 ligne de UsedRange.

 

[bsalv]

union prend trop de temps, si le nombre de "areas" est assez grand, donc là, il y a des possibilités, mais cela vaut la peine ?

 

[Dudu2]

C'est vrai Union prend de plus en plus de temps au fur et à mesure que s'accumulent les Unions.
Mais dans son code point n'est besoin d'Union... On gagne 0.3 à 0.5 secondes en gros, le plus long c'est la création / deletion du Workbook temporaire.

Function RangeVisibleCells(MyWorksheet As Worksheet) As Range
Dim wkb As Workbook, wks As Worksheet, xarea As Range, x As Range, res As Range
    Application.ScreenUpdating = False                ' blocage affichage
    Set wkb = Workbooks.Add: Set wks = wkb.Sheets(1)  ' nouveau classeur
    On Error GoTo Menage                              ' si erreur
    MyWorksheet.Rows.Copy: wks.Rows.PasteSpecial xlPasteFormats         ' copie des lignes de la feuille vers le nouveau classeur
    Application.CutCopyMode = False
    Set res = wks.Cells.SpecialCells(xlCellTypeVisible)
 
Menage:
   If Not res Is Nothing Then Set RangeVisibleCells = MyWorksheet.Range(res.Address(0, 0))     ' on affecte les plages visibles à la fonction
   wkb.Close savechanges:=False     ' on supprime le classeur auxiliaire
End Function

 

[patricktoulon]

re
Bonjour
Moi j'ai du mal à comprendre peut-être suis-je victime d'incident cérébral(je téléphone au veto tout de suite pour prendre rendez-vous).
Mais je me demande comment le SpecialCells(xlCellTypeVisible) d'une feuille dans un classeur créé dynamico avec la copy du classeur concerné.
Peut-être plus rapide que la même chose sur le classeur concerné lui-même
Après je le redis peut-être suis-je victime d'un incident cérébral
......
2minute plus tard
voilà j'ai pris rendez-vous avec mon veto j'ai un rendez-vous mardi 45 décembre 2025 pour une coloscopie.

LOL.......

 

[Dudu2]

Bonjour @patricktoulon,

Peut-être plus rapide que la même chose sur le classeur concerné lui-même

C'est sûr que ce serait plus simple... si la feuille du classeur en question n'était pas protégée par un mot de passe inconnu du code.
C'est pour ça que @DuduCros et @mapomme se décarcassent.

Autre chose d'important !
En fait le code du Post précédent (que j'ai modifié du code de @mapomme pour éviter les Union()) est complètement faux.
Raison: dès qu'on manipule des Range.Address, il faut se souvenir que le string Address est limité/tronqué en longueur à 257 et que c'est pour ça que dans mes fonctions sur Range j'avais fait les fonctions GetRangeFromAddress et GetAddressFromRange pour lever ces limites.

Donc soit on utilise le code natif de @mapomme avec ces Union() d'Areas, soit on utilise le code ci-dessous qui a besoin des fonctions de Range où il y aura aussi potentiellement des Union() à cause des longueurs d'Address > 257 mais en bien moins grand nombre.
Comme la différence en temps d'exécution est minime (5/100ème de seconde), le code de @mapomme est parfait.

 

[patricktoulon]

une video parle mieux que les mots même si j'en dit quelques uns

Pour afficher ce contenu, nous aurons besoin de votre consentement pour définir des cookies tiers.
Pour plus d'informations, consultez notre page sur les cookies.

Accepter les cookies de tiers

 

[mapomme]

Bonjour @patricktoulon🙂,

Après je le redis peut-être suis-je victime d'un incident cérébral ......

[humour] Je n'aurais jamais pu écrire cette phrase. La penser, je ne dis pas 🤪😜🤣 [/humour]

@Dudu2 a parfaitement explicité le pourquoi de l'emploi des Unions. Je me suis fait "avoir" tant de fois !!! Et je ne suis pas à l'abri de refaire la bourde. C'est tellement contre-intuitif de devoir penser à la limitation en taille de l'adresse d'un range😮.

Si on ne se croise pas d'ici le 25 décembre, bonnes fêtes de Noël à vous.

 

[mapomme]

Re @patricktoulon 🙂,

Le problème est de trouver le range des cellules visibles d'une feuille du moins c'était la question initiale.
Ca n'a rien à voir avec le UsedRange, du moins me semble-t-il.

Exemple:

• une feuille vide
• on masque toutes les colonnes depuis la seconde jusqu'à l'avant dernière (seules les colonnes A et XFD sont visibles)
• on masque toutes les lignes depuis la ligne 2 jusqu'à l'avant dernière (seules les lignes 1 et et 1 048 576 sont visibles)
• ActiveSheet.UsedRange.Address renvoie "A1" (ce qui est déjà étrange). Hors, il existe un tas de colonnes masquées après la colonne A et de lignes masquées après la ligne 1 qui sont en dehors de UsedRange
• donc pour le range des plages visibles, il faut tester toutes les colonnes (et lignes) même si elles ne sont pas dans le UsedRange car même en dehors du UsedRange il peut s'y trouver des colonnes ou lignes masquées.

Du moins c'est ce que crois, non ?

 

[Dudu2]

@patricktoulon,
Tu n'as pas suivi le truc depuis le début, alors tu prends des raccourcis.
Le but initial c'est de trouver les cellules visibles de la feuille, même si la plupart du temps, comme l'a indiqué @bsalv, on s'intéresse plutôt aux cellules visibles du UsedRange.
Toi tu t'intéresses aux lignes, mais ce n'est pas suffisant car il y a aussi les colonnes.

Le sujet a 2 solutions, la mienne et celle de @mapomme.

La mienne se base aussi sur un parcours des lignes du UsedRange et leur propriété .Hidden.
Le souci avec ce parcours, c'est que plus le UsedRange a de lignes, plus le temps elapse augmente.
Pour 100.000 lignes c'est 0.4 seconde et pour 1.000.000 de lignes c'est 4 secondes. Ça fait un peu long !
Alors certes 1.000.000 de lignes c'est rare, mais c'est possible, non seulement à cause des données mais aussi à cause de masquages partiels de lignes qui augmentent de facto le UsedRange. Et puis la fonction est généraliste et doit, par principe, considérer tous les cas.

La solution de @mapomme, quelque soit le masquage et la taille du UsedRange qui n'entre pas en ligne de compte est à peu près constante en temps elapse d'environ 0.25 seconde.

Tu vois l'intérêt de la copie de feuille ?