Pallier la limite de la méthode SpecialCells de VBA/Excel
Soyez le premier à donner votre avis sur cette source.
Snippet vu 3 301 fois - Téléchargée 10 fois
Contenu du snippet
La méthode VBA Specialcells applicable à un objet Range de Excel est utile et très pratique.
Elle a cependant une limite gênante : elle ne peut recenser d'un seul coup plus d'un certain nombre d'aires (areas) résultant de son application. Ce nombre est égal à la moitié de la limite d'un type integer. Pourquoi la moitié ? Tout simplement parce-qu'il lui faut prévoir le pire des cas de fractionnement : une cellule sur deux correspondant au critère appliqué à la méthode.
Comment pallier cet inconvénient ? En traitant par blocs de cellules dont le nombre n'excède pas la moitié de la limite d'un type integer.
Ce qui veut clairement dire qu'il va falloir diviser encore par le nombre de colonnes de la plage. C'est ce que fait le petit code/snippet qui suit :
1) la fonction :
2) un exemple d'utilisation :
Elle a cependant une limite gênante : elle ne peut recenser d'un seul coup plus d'un certain nombre d'aires (areas) résultant de son application. Ce nombre est égal à la moitié de la limite d'un type integer. Pourquoi la moitié ? Tout simplement parce-qu'il lui faut prévoir le pire des cas de fractionnement : une cellule sur deux correspondant au critère appliqué à la méthode.
Comment pallier cet inconvénient ? En traitant par blocs de cellules dont le nombre n'excède pas la moitié de la limite d'un type integer.
Ce qui veut clairement dire qu'il va falloir diviser encore par le nombre de colonnes de la plage. C'est ce que fait le petit code/snippet qui suit :
1) la fonction :
Private Function plage_specialcells(p As Range, nature As Integer, typ As Byte) As Range
Dim plage_bloc As Range, nb As Long, taille_bloc As Integer, nb_blocs As Integer, dep As Long, fin As Long, i As Long
nb = p.Rows.Count
taille_bloc = 16385 ' à votre gré entre 2 et 16385 maximum : taille de bloc à traiter en boucle
taille_bloc = taille_bloc p.Columns.Count ' car le nb de cellules d'une ligne est celui de ses colonnes
nb_blocs = nb taille_bloc
If typ = 0 Then typ = 2
For i = 0 To nb_blocs + 1
dep = p.Offset(i * taille_bloc).Row '- 1
fin = dep + taille_bloc - 1
If fin > nb Then fin = nb
On Error Resume Next ' pour le cas ou aucune cellule concernée
Set plage_bloc = Range(p.Cells(dep, 1), p.Cells(fin, p.Columns.Count)).SpecialCells(nature, typ)
On Error GoTo 0
If Not plage_bloc Is Nothing Then
If plage_specialcells Is Nothing Then
Set plage_specialcells = plage_bloc
Else
Set plage_specialcells = Union(plage_specialcells, plage_bloc)
End If
End If
If fin >= nb Then Exit Function
Next
End Function
2) un exemple d'utilisation :
Application.ScreenUpdating = False
Dim nature As Integer, typ As Byte, plage_traitee As Range, plage_desti As Range
Set plage_traitee = Range("A1:B50123") ' ===>> ici la plage à traiter
nature = xlCellTypeBlanks ' par exemple (ou autre nature de votre choix)
typ = 0 ' ===>> mettre 0 pour tous types ou 16 (xlErrors) ou 4 (xlLogical ou 1 (xlNumbers) ou 2 (xlTextValues)
Set plage_desti = plage_specialcells(plage_traitee, nature, typ)
Application.ScreenUpdating = True
'============== le reste n'est là que pour visualiser le résultat, si on le souhaite
If Not plage_desti Is Nothing Then plage_desti.Select Else MsgBox "aucune correspondance"Compatibilité : VBA/Excel - toutes versions
- Messages postés
-
18038
- Date d'inscription
- lundi 7 décembre 2009
- Statut
- Modérateur
- Dernière intervention
- 11 avril 2018
235Aucune réaction ...
J'en attendais toutefois une !
Et je vais donc vous y aider, à réagir :
Si, de cette manière, nous avons passé un certain cap "liminaire" (aller au delà de ce que permet normalement l'application sans tremplins de la méthode Specialcells) nous n'avons pas réellement énormément avancé, même si l'avancée est déjà assez significative et suffisante dans la plupart des contextes.
Pourquoi ? parce-que nous nous heurtons à une seconde limite : celle de la méthode Union qui, elle également, connaît des limites. Avec, pour corollaire, l'impossibilité d'utiliser en l'état cette méthode palliative au delà d'un SECOND nombre de "sous-plages" de la plage extraite. Il se trouve que cette seconde limite se situe autour de la cinquantaine de milliers d'aires (objets areas d'une plage).
A quoi servirait-il, dans de telles conditions, de "dresser" une plage qui, elle-même limitée, en limiterait les actions (suppression, sélection ou autres) qui lui seraient appliquées au delà d'un certain nombres d'aires ? Et quelle est exactement cette nouvelle limite ?
Que faudrait-il faire pour passer ce nouveau cap ?
Je m'y mets de mon côté, mais aimerais beaucoup que d'autres tentent d'en faire autant.
Vous n'êtes pas encore membre ?
inscrivez-vous, c'est gratuit et ça prend moins d'une minute !
Les membres obtiennent plus de réponses que les utilisateurs anonymes.
Le fait d'être membre vous permet d'avoir un suivi détaillé de vos demandes et codes sources.
Le fait d'être membre vous permet d'avoir des options supplémentaires.