XL 2021 VBA Alternative à SpecialCells(xlCellTypeVisible)

B

bsalv

XLDnaute Occasionnel

• 18 Décembre 2025

• #31

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.

 

Vote positif 0 Vote négatif

Dudu2

XLDnaute Barbatruc

• 18 Décembre 2025

• #32

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.

 

Dernière édition: 18 Décembre 2025

Vote positif 0 Vote négatif

Dudu2

XLDnaute Barbatruc

• 18 Décembre 2025

• #33

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

Cliquez pour agrandir...

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.

 

Vote positif 0 Vote négatif

B

bsalv

XLDnaute Occasionnel

• 18 Décembre 2025

• #34

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

Cliquez pour agrandir...

okay, pour moi, cela suffit aussi ...

 

Vote positif 0 Vote négatif

mapomme

XLDnaute Barbatruc

Supporter XLD

• 18 Décembre 2025

• #35

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".

VB:

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

 

Pièces jointes

• Dudu2- Cellules visibles- v1b.xlsm

  20.6 KB · Affichages: 6

Vote positif 0 Vote négatif

Solution

Dudu2

XLDnaute Barbatruc

• 18 Décembre 2025

• #36

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.

 

Pièces jointes

• VisibleCells (quand SpecialCells(xlCellTypeVisible) plante erreur 1004 cause feuille protégée).xlsm

  53.9 KB · Affichages: 0

Dernière édition: 18 Décembre 2025

Vote positif 0 Vote négatif

Dudu2

XLDnaute Barbatruc

• 18 Décembre 2025

• #37

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.

 

Dernière édition: 18 Décembre 2025

Vote positif 0 Vote négatif

B

bsalv

XLDnaute Occasionnel

• 18 Décembre 2025

• #38

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 ?

 

Vote positif 0 Vote négatif

Dudu2

XLDnaute Barbatruc

• 18 Décembre 2025

• #39

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.

VB:

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

 

Pièces jointes

• Dudu2- Cellules visibles- v1b.xlsm

  22.9 KB · Affichages: 0

Dernière édition: 18 Décembre 2025

Vote positif 0 Vote négatif

patricktoulon

XLDnaute Barbatruc

• 19 Décembre 2025

• #40

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.......

 

Vote positif 0 Vote négatif

Dudu2

XLDnaute Barbatruc

• 19 Décembre 2025

• #41

Bonjour @patricktoulon,

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

Cliquez pour agrandir...

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.

 

Pièces jointes

• Dudu2- Cellules visibles- v1b.xlsm

  38.8 KB · Affichages: 0

Dernière édition: 19 Décembre 2025

Vote positif 0 Vote négatif

patricktoulon

XLDnaute Barbatruc

• 19 Décembre 2025

• #42

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

 

Vote positif 0 Vote négatif

mapomme

XLDnaute Barbatruc

Supporter XLD

• 19 Décembre 2025

• #43

Bonjour @patricktoulon🙂,

patricktoulon à dit:

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

Cliquez pour agrandir...

[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.

 

Vote positif 0 Vote négatif

mapomme

XLDnaute Barbatruc

Supporter XLD

• 19 Décembre 2025

• #44

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 ?

 

Vote positif 0 Vote négatif

Dudu2

XLDnaute Barbatruc

• 19 Décembre 2025

• #45

@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 ?

 

Dernière édition: 19 Décembre 2025

Vote positif 0 Vote négatif