Discussion :

[gnain]

comme ça aussi ça génère un erreur si il en trouve pas

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Union(rng.SpecialCells(xlCellTypeConstants), rng.SpecialCells(xlCellTypeFormulas)).Select

le mieux serais ton code avec on erreur resume next

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
Dim rng As Range, RgAdresse, adresseFinal, dicoRange
    Set rng = Cells
    Set dicoRange = CreateObject("Scripting.Dictionary")

   On Error Resume Next
    Union(rng.SpecialCells(xlCellTypeConstants), rng.SpecialCells(xlCellTypeFormulas)).Select
    If Err.Number Then rng.SpecialCells(xlCellTypeConstants).Select
    On Error GoTo 0
    RgAdresse = Split(Selection.Address, ",")
    For i = 0 To UBound(RgAdresse)
        Set region = Range(RgAdresse(i), RgAdresse(i)).CurrentRegion
        If Not dicoRange.exists(region.Address) Then
            dicoRange(region.Address) = "(" & RgAdresse(i) & ","
        Else
            dicoRange(region.Address) = dicoRange(region.Address) & RgAdresse(i) & ","
        End If
    Next
    For Each elem In dicoRange
        dicoRange(elem) = Replace(dicoRange(elem) & ")", ",)", ")")
        'MsgBox elem & " :" & dicoRange(elem)
        adresseFinal = adresseFinal & "  " & dicoRange(elem)
    Next
    MsgBox adresseFinal

[davido84]

Bonsoir,
je n'ai pas tout suivi donc je suis peut-être à côté de la plaque mais au cas où, à tester :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
 
Sub PlgNonContig()
Dim Plg As Range, c As Range
 
For Each c In ActiveSheet.UsedRange
  If c.Value2 <> vbNullString Then
    If Plg Is Nothing Then Set Plg = c Else Set Plg = Union(c, Plg)
  End If
Next
 
If Not Plg Is Nothing Then MsgBox Plg.Address
End Sub

Cette procédure ramène les plages non contiguës dans l'ordre inverse (c'est dû à l'ordre de traitement).
Cela peut suffire ou nécessiter un traitement supplémentaire en fonction de ce que tu veux en faire.
A+
David

[patricktoulon]

Mais non!!!
Pourquoi attendre une erreur pour bifurquer
on peut tres bien poser la question "Mr sheets Vous qui ette de ce classeur avez vous une ou des cellules avec des formules dans la grille SVP répondez moi "

a mon avis il devrait te repondre un peu comme ceci:
dans le cas qui nous concerne

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
If Not Cells.Find("*", LookIn:=xlFormulas) Is Nothing Then
        Union(rng.SpecialCells(xlCellTypeConstants), rng.SpecialCells(xlCellTypeFormulas)).Select
    Else
        rng.SpecialCells(xlCellTypeConstants, 23).Select
    End If

bon alors Philippe on y est ou on y est pas ?

[gnain]

Bonjour patricktoulon,
oui bien sûr il vaut mieux avec un if then else

davido84
votre code fonctionne peut importe le type même pour les lignes filtrées
J'opte pour ton code +1

[patricktoulon]

sacré Davido y nous met la rouste

voila une adaptation de ma mequanique sur la selection de Davido

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
Sub essai3()
Dim Plg As Range, c As Range, RgAdresse, adresseFinal, dicoRange
 Set dicoRange = CreateObject("Scripting.Dictionary")
For Each c In ActiveSheet.UsedRange
  If c.Value2 <> vbNullString Then
    If Plg Is Nothing Then Set Plg = c Else Set Plg = Union(c, Plg)
  End If
Next
If Not Plg Is Nothing Then
 RgAdresse = Split(Plg.Address, ",")
    For i = 0 To UBound(RgAdresse)
        Set region = Range(RgAdresse(i), RgAdresse(i)).CurrentRegion
        dicoexist = dicoRange.exists(region.Address) = True
        dicoRange(region.Address) = IIf(dicoexist, dicoRange(region.Address) & RgAdresse(i) & ",", "(" & RgAdresse(i) & ",")
    Next
    For Each elem In dicoRange
        dicoRange(elem) = Replace(dicoRange(elem) & ")", ",)", ")")
        'MsgBox elem & " :" & dicoRange(elem)
        adresseFinal = adresseFinal & "  " & dicoRange(elem)
    Next
 End If
    MsgBox adresseFinal
End Sub

et voila la mienne légèrement modifier

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
Sub essai()
    'MsgBox Application.CountIf(Cells, Cells.SpecialCells(xlCellTypeFormulas))
    Dim rng As Range, RgAdresse, adresseFinal, dicoRange
    Set rng = Cells
    Set dicoRange = CreateObject("Scripting.Dictionary")
     If Not Cells.Find("*", LookIn:=xlFormulas) Is Nothing Then
        Union(rng.SpecialCells(xlCellTypeConstants), rng.SpecialCells(xlCellTypeFormulas)).Select
    Else
        rng.SpecialCells(xlCellTypeConstants, 23).Select
    End If
    RgAdresse = Split(Selection.Address, ",")
    For i = 0 To UBound(RgAdresse)
        Set region = Range(RgAdresse(i), RgAdresse(i)).CurrentRegion
        dicoexist = dicoRange.exists(region.Address) = True
        dicoRange(region.Address) = IIf(dicoexist, dicoRange(region.Address) & RgAdresse(i) & ",", "(" & RgAdresse(i) & ",")
    Next
    For Each elem In dicoRange
        dicoRange(elem) = Replace(dicoRange(elem) & ")", ",)", ")")
        'MsgBox elem & " :" & dicoRange(elem)
        adresseFinal = adresseFinal & "  " & dicoRange(elem)
    Next
    MsgBox adresseFinal
End Sub

Allo !!! Philippe on y est la maintenant je crois non?
Davido
ps j'ai oublie celle la aussi

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

if Application.CountIf(Cells, Cells.SpecialCells(xlCellTypeFormulas))<>0 then .....

[davido84]

Bonjour,
bon finalement j'avais donc compris ce que vous cherchiez...tant mieux.
L'intérêt que je vois dans cette possibilité c'est que l'on reste dans un range que l'on peut manipuler comme un Range. Si l'unique intérêt est de ramener une chaîne correspondant aux adresses de cette plage on peut bien sûr utiliser d'autres moyens mais on n'est plus dans un objet Range et l'on perd donc la possibilité d'en exploiter ses propriétés et méthodes.
Si vous ouvrez la propriété Areas de l'objet Plg vous y trouverez bien le nombre de plages discontinues, ce que l'on n'obtient pas avec UsedRange.
Maintenant si l'on veut absolument les avoir dans l'ordre des colonnes et non des lignes :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
 
Sub PlgNonContig()
Dim Plg As Range, c As Range
 
For Each c In ActiveSheet.UsedRange
  If c.Value2 <> vbNullString Then
    If Plg Is Nothing Then Set Plg = c Else Set Plg = Union(c, Plg)
  End If
Next
 
If Not Plg Is Nothing Then MsgBox Plg.Address
 
Dim i As Long, Plg2 As Range
 
For i = 1 To Plg.Areas.Count
  If Plg2 Is Nothing Then Set Plg2 = Plg.Areas.Item(i) Else Set Plg2 = Union(Plg.Areas(i), Plg2)
Next i
 
If Not Plg2 Is Nothing Then MsgBox Plg2.Address
 
End Sub

Mais est-ce utile dans ton cas ? Je n'en sais rien.
A+
David

[patricktoulon]

Je viens d'essayer la version de Davido sur une plage plus importante 30000 ligne

une boucle sur un range est relativement assez long dans le cas d'une plage assez grande


les fonctions avec specialcell(xltype) restent elles les plus rapides

il faut en tenir compte dans le cadre du développement d'une application

[Philippe Tulliez]

Bonjour à tous,
Par ordre d'entrée en scène, je remercie Denis, Gnain, Patrick, Docmarti, Mercatog, Eric, David pour leurs différentes réponses.
En posant la question, j'espérais avoir louper une propriété de l'objet feuille qui m'aurait permis d'avoir directement les adresses non contiguës des plages utilisées dans une feuille de calcul.
Tout cela pour éviter de passer par une boucle sur la collection Areas et de faire un CurrentRegion sur chaque élément de celle-ci
Vos différentes réponses me confirment que cette propriété n'existe pas ou n'est pas connues.
Voici la procédure que j'ai écrite et qui est proche de plusieurs propositions faites dans cette discussion.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
Sub AreaInUsedRange()
 ' Déclaration des variables
 Dim rngUsed As Range, rngArea() As Range, rngRegion As Range
 Dim a As Byte, count As Byte, c As Byte, flag As Boolean
 ' Référence UsedRange
 With shtDb.UsedRange: Set rngUsed = .SpecialCells(xlCellTypeConstants): End With
 ' Boucle sur les zones non contiguës
 For a = 1 To rngUsed.Areas.count
  Set rngRegion = rngUsed.Areas(a).CurrentRegion
  If a > 1 Then
    For c = 1 To UBound(rngArea)
     flag = Not Intersect(rngRegion.Item(1), rngArea(c)) Is Nothing: If flag Then Exit For
    Next
  End If
  If Not flag Then count = count + 1: ReDim Preserve rngArea(count): Set rngArea(count) = rngRegion
 Next a
 ' Affiche les adresses des plages non contiguës
 For a = 1 To UBound(rngArea)
   Debug.Print Format(a, "00 )") & rngArea(a).Address
 Next
End Sub

Dans la ligne 8 de cette procédure je ne prends en compte que les constantes parce-que toutes mes plages de cellules ont une ligne avec des étiquettes de colonnes mais on peut remplacer cette ligne par

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

With shtDb.UsedRange: Set rngUsed = Union(.SpecialCells(xlCellTypeConstants), .SpecialCells(xlCellTypeFormulas)): End With

je considère cette discussion comme résolue.

[davido84]

Oui effectivement, sur une grande plage cela peut être lent en utilisant toute la plage du UsedRange...donc en travaillant avec la propriété SpecialCells à l'intérieur de cette plage :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
Sub PlgNonContig()
Dim Usplg As Range, Plg As Range, i As Long
 
Set Usplg = ActiveSheet.UsedRange
On Error Resume Next
Set Plg = Union(Usplg.SpecialCells(xlCellTypeConstants), Usplg.SpecialCells(xlCellTypeFormulas))
 
If Not Plg Is Nothing Then
  For i = 1 To Plg.Areas.Count
    Debug.Print Plg.Areas.Item(i).Address
  Next i
End If
 
End Sub

A tester plus avant.

A+

Edit : désolé Philippe mais je n'avais pas rafraîchi avant de poster. Je n'ai pas encore étudié ta solution mais si tu as trouvé ton bonheur tant mieux.

[patricktoulon]

d'accords Philipe comme tu veut on considère que c'est résolu

mais ton résultat est faux il prend en compte des cellules blanche(rien dedans)

pour ce résultat il te suffisait de couper mon code en deux par exemple ,au niveau de la ligne utilisant le currentregion et tu aurais eu le même résultat

plusieurs solution que l'on t'a donné respectes cela (pas de cellules blanche )
et en plus tu boucle sur un range souhaitons que le usedrange fasse pas toute la feuille

tandis que les solutions que l'on a proposé boucle sur un string obtenu par les fonction specialcell et xltype

beaucoup plus rapides et exact surtout

[patricktoulon]

Davido
terter ta derniere proposition
elle ne rassemble pas les cellules isolé sur une colonne avec les mitoyenne sur la colonne a coté
le résultat est donc faux

tester avec l'exemple de sheets que j'ai mis en capture d'écran plus haut
resultat

$D$4
$B$6
$C$5:$C$7
$H$6:$H$8
$H$9:$I$9
$E$13:$E$14
$F$12:$F$15
$D$7

alors que l'on devrait avoir ceci:

$B$6,$C$5:$C$7,$D$4,$D$7
$H$6:$H$8,$H$9:$I$9
$E$13:$E$14,$F$12:$F$15

c'est ce que donne ma proposition avec le dico

[patricktoulon]

philippe
si c'est seulement le currentregion que tu voulais
allez une petite dernière rassemblant les diverses

on teste directement la cel et le currentregion dans une seul boucle sur le usedrange et collection de non doubles dans le dico et basta

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
Sub essai4()
    Dim dicoRange, rNg As Range, cel, elem As Variant
    Set dicoRange = CreateObject("Scripting.Dictionary")
    With ActiveSheet
        Set rNg = .UsedRange
        For Each cel In rNg.Cells
            If cel.Value2 <> vbNullString And dicoRange.exists(cel.CurrentRegion.Address) = False Then dicoRange(cel.CurrentRegion.Address) = ""
        Next
        For Each elem In dicoRange
            Debug.Print elem
        Next
    End With
 Set dicoRange = Nothing
End Sub

voila

[davido84]

elle ne rassemble pas les cellules isolé sur une colonne avec les mitoyenne sur la colonne a coté
le résultat est donc faux

Non pour moi le résultat n'est pas faux puisque l'ensemble des cellules sont trouvées simplement le mode de traitement fait que la priorité est donnée aux lignes et non aux colonnes. Mais d'après ce que je vois des résultats que tu ramènes on retrouve les mêmes cellules mais quelquefois dans un ordre différent. Après je ne sais pas si c'est important pour le demandeur. Dans l'absolu et a priori je dirais non puisque l'ensemble des plages discontinues sont ramenées et donc une fois que tu as récupéré ces zones dans un Range tu peux en faire ce que tu veux. Mais si c'est important pour le demandeur il faudra alors un traitement supplémentaire (mais a priori je n'en vois pas l'intérêt).

Quant au fait de passer par un dictionnaire, pourquoi pas si l'objectif est de ramener une chaîne de caractères représentant les plages discontinues mais par contre dans ce cas-là tu ne peux plus manipuler ces zones comme un Range puisque tu en as fait un String. Donc tout dépend du but recherché.
A+

[patricktoulon]

re

QUI,QUOI,COMENT??????

en string pas en string ,on peut pas traiter la plage en string faux pas faux
whoaoh!!!!

depuis quand on peut pas faire quelque chose du genre

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
for each elem in dicoRange
range(elem).interior.colorindex=(rnd*50+1)
next

alors on peut pas traiter ????

ensuite il faudrait se mettre d'accords
currentregion contiens la plage contigues mais pas l'inverse . et la j'ai tout dis
dans cette capture d'écran regarde les cellules ou elle sont pas vide
currentregion donne par exemple pour la première plage B4D7
hors b4,b5,c4,b7,d5,d6 ne font pas parti de la plage contiguës utilisé elle sont bien dans le currentregion mais pas dans les contiguës puisqu'elles ne sont pas utilisées
allez encore une fois
currentregion contiens la plage contiguës mais pas l'inverse

on la voit la phrase la???
Pièce jointe 173084

ps:

je viens de m'apercevoir qu'en fait tu n'avais pas compris ma démarche avec le dico

si tu regarde bien dans la boucle visitant les cells la variable cel est un object(cells) pas un string

de meme que si je veux aller directement sur les plage donc en object

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

if dico.exists(cel.curentregion)=false then

(on ajoute dans le dico) et ":" et

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

  cel.currentregion.interiro.....=..blablabla

comme tu peut le voir je travaille sur l'object et directement en amont l'ors de la détermination des régions
en fait je procède très souvent comme ca
le dico me sert de condition
sa m'évite les if/elseif/endif qui souvent vont par 3 les deux premier sont gérable mais le 3 Emme est parfois difficile dans une boucle le dico arrange tout
si c'est oui on fait si c'est non on fait pas

poil au doigts

[Philippe Tulliez]

Bonjour à tous,
Au risque de me répéter, le but de ma question était de savoir s'il existait une propriété que je ne connaissait pas et native d'excel qui aurait pu me renvoyer la collection d'un groupe de plages de cellules faisant partie des cellules utilisées. Style maFeuilleUsedRange.Areas mais comme j'avais testé cette collection je me suis rendu compte qu'elle ne fonctionnait que si au préalable on utilsait UNION.
C'est la raison que pour ma part ayant ouvert la discussion dans ce but, je considérais cette discussion comme résolue faute d'une réponse sur cette propriété manifestement inexistante.

[patricktoulon]

re
Philippe rien ne t'empêche d'en faire une fonction générique
surtout que la il n'est pas nécessaire d'avoir une grosse mécanique

le sujet est intéressant
diverses solutions existe on viens d'en explorer quelque unes
pour le moment je me bas avec Davido pour lui faire comprendre la différence entre currentregion et contigues
mais sans vouloir de bombarder la tête pour moi le dernier exemple que tu poster ne répond pas exactement a ta demande initiale

[Philippe Tulliez]

Bonjour Patrick,
Je n'ai pas de problème que la discussion continue sur le sujet mais je crois que la charte du forum prévoit que l'initiateur de la discussion indique comme "résolu" la discussion si la réponse est satisfaisante ou dans mon cas que manifestement ce que j'attendais n'est pas possible.

[davido84]

Re

Merci Patrick mais je sais ce que recouvre la propriété CurrentRegion d'un Range.

Je vais prendre un exemple concret pour mieux me faire comprendre : le but de Philippe était de voir s'il existait une propriété Areas dans un UsedRange, de type maFeuilleUsedRange.Areas.
En fait elle existe mais ne renvoit qu'une seule plage quel que soit le nombre de plages discontinues qu'elle contient.

Quand tu testes ma 1ère proposition en plaçant un point d'arrêt sur End Sub, que tu ouvres la fenêtre des variables locales et que tu ouvres l'objet Plg puis Areas tu constates que tu as bien un ensemble d'items correspondant au nombre de sous-plages non vides (36 dans le cas présent mais je précise que c'est un UsedRange qui n'a rien à voir avec la copie d'écran que tu as postée).



Tu obtiens donc au final un objet UsedRange possédant l'ensemble des Areas et tu peux donc ensuite travailler immédiatement sur les différents items de Areas de cet objet (ou sur toute autre propriété d'ailleurs). Bref tu as à ta disposition un objet range fonctionnel avec l'ensemble des items de Areas répertoriés et accessibles et que tu peux donc manipuler comme tu le veux.

Si je fais le même test en plaçant un point d'arrêt sur le End Sub de ta Sub essai et que j'ouvre les 2 objets Range (rng et region) je ne retrouve nulle part l'ensemble des items de Areas. Je ne peux donc en l'état utiliser aucun des objets range pour travailler sur l'un des items de Areas puisque je ne les ai pas à ma disposition.

A+
David

[patricktoulon]

tu a leur adresses a disposition dans le dico (dons en mémoire )
dans la boucle du split sur les adresse obtenu par le xltype tu met "range(" et a la fin tu met"")" et tu fais ce que tu veux avec et en mémoire en plus puisque que c'est un dico donc vélocité accru
ps :quand tu travaille avec un dico ou un tableau(variable) tu travaille en mémoire ca m'étonnerait que tu le retrouve dans la fenêtres des variables locales

en ce qui concerne la différence d'adresse entre le current region et les adresse obtenues avec ma méthode je persiste a dire que mon résultat reste le plus exacte

mais bon ca n'a pas l'air de rentrer alors pour ce soir on va en rester la

ma méthode peut être pas ortodoxe pour toi mais elle ramène les vraies plages contiguës et cela est incontestable
tu peut tourner ca dans tout les sens
le résultat dans le debug.print parle de lui même

je dis pas que la mienne est la meilleur je dis que mon résultat lui est juste


sinon a quoi bon faire tout un ramdam pour obtenir un current region ;fonction excel vba qui existe déjà

enfin de toute façon je suis en train de potiner un truc ca va vous plaire laisser moi un jour ou deux pour la réduction de lignes de codes

[Philippe Tulliez]

Bonjour David,

Je vais prendre un exemple concret pour mieux me faire comprendre : le but de Philippe était de voir s'il existait une propriété Areas dans un UsedRange, de type maFeuilleUsedRange.Areas.
En fait elle existe mais ne renvoie qu'une seule plage quel que soit le nombre de plages discontinues qu'elle contient.

Tu as raison et j'aurais dû être plus explicite car en effet, j'avais constaté que je n'obtenais qu'une seule plage dans le cas d'un UsedRange en utilisant la collection Areas.
Ce constat provient du fait que je suis en train de modifier une procédure écrite il y a longtemps Code pour éliminer les espaces (TRIM) dans un tableau et dont un des arguments est un objet qui peut être soit une feuille soit une plage de cellules.
Cette procédure me convenait parfaitement car j'ai pour habitude pour des feuilles de données d'avoir une plage par feuille et des étiquettes de colonnes en première ligne. Les seuls cas où j'ai plusieurs plages sur une feuille c'est pour les tableaux de bord et les feuilles paramètres. Plages qui elles même ont des étiquettes de colonnes.
Dans le cas où j'ai plusieurs plages j'envoie les adresses des plages par Union et je n'ai jamais eu de problème.
Il se fait qu'un utilisateur de ma procédure, qui est publique, envoie comme paramètre l'objet feuille avec comme paramètre UsedRange et pendant son utilisation a rencontré d'autres problèmes (plages filtrées, tableaux croisé dynamique, etc.)
Je suis donc en train de refaire une analyse complète de cette procédure afin de prévoir le maximum de situations que je pourrais rencontrer sur une feuille.
Je pouvais bien entendu détecter combien de listes (Collection ListObjects) et de tableaux croisés dynamiques (PivotTables) sont présents sur la feuille mais j'ai voulu plutôt aborder le sujet au départ des objets Range d'où ma question initiale "Y aurait-il nativement une propriété qui aurait pu me renvoyer l'ensemble des plages à l'intérieur d'un UsedRange ?".