Limite de nombre de cellules dans "range"

[Blafi]

Bonjour à tous,

Pour sélectionner les cellules de ma feuille dont le fond est en gris j'utilise une macro trouvée sur ce forum (que je mets ci-dessous).
Cette macro fonctionne très bien mais je m'aperçois qu'au dessus d'un certain nombre de cellules, j'ai une erreur sur l'objet range (lorsque le nombre de cellules à sélectionner est supérieur à moins d'une centaine..)
Quelqu'un peut-il me dire quelle est la limitation du nombre de cellules dans un objet Range.select ?
Et dans le cas ou il y a une limite, comment faire pour sélectionner toutes mes cellules, qui sont souvent plus d'une centaine ? En plusieurs fois sans doute mais comment?
Merci de votre aide..

Public Sub SelCelGris()
'selectionne les cellules grises

Dim c As Range, CellGris As String
For Each c In Range("C3:C2000")
If c.Interior.ThemeColor = xlThemeColorDark2 Then
CellGris = CellGris & "," & c.Address
End If
Next

MsgBox CellGris 'jusque là tout va bien
Range(Mid(CellGris, 2, Len(CellGris) - 1)).Select 'là j'ai un refus

End Sub

 

[david84]

Re : Limite de nombre de cellules dans "range"

Bonjour,

mais je m'aperçois qu'au dessus d'un certain nombre de cellules

A partir de quelle cellule cela plante ?

Que ramène

MsgBox CellGris

à ce moment ?

Que ramène

MsgBox Range(Mid(CellGris, 2, Len(CellGris) - 1))

également ?
Si cette valeur ne correspond pas à un Range c'est normal que cela plante.
A+

 

[Blafi]

Re : Limite de nombre de cellules dans "range"

Merci david84 pour ta réponse :

- la limite à partir de laquelle ça "coince" est difficile à connaitre car ce n'est pas le CellGris qui coince mais le moment de sélectionner les cellules correspondantes avec range en enlevant la virgule en début de Cellgris et en gardant le reste (objet de la fonction Mid(..). J'ai essayé avec 50, puis 80 cellules, puis 90.. et là ça coince. Donc la limite est entre 80 et 90 adresses de cellules

-j'ai essayé différents nombres de cellules à sélectionner, mon CellGris (en string) ne pose pas de pb et me renvoie bien une suite d'address de cellules commençant par une "virgule que Mid élimine pour la suite..

-la fonction mid me ramène des chaines comme (address1,address2,adress3 etc..) mais range plante et je pense que le nombre d'adresses est trop important. ai-je raison ??

Je vais essayer de faire en plusieurs fois car le but est de copier les lignes entières de ces cellules dans un autre classeur, donc je vais tenter de le faire en 2 ou trois fois, le plus dur étant de faire reprendre la macro là où elle s'est arrétée avant..

Ci-joint un fichier très épuré de ses données mais avec mes macros et un certains nombre de cellules en gris.. Le "Range" coince en effet entre 80 et 90 adresses. Pourquoi ? là est le mystère...
A ++

merci et à ++

 

[david84]

Re : Limite de nombre de cellules dans "range"

Tu dis

MsgBox CellGris 'jusque là tout va bien
Range(Mid(CellGris, 2, Len(CellGris) - 1)).Select 'là j'ai un refus

donc avant le plantage

MsgBox CellGris

te ramène quoi ?
A+

 

[Pierrot93]

Re : Limite de nombre de cellules dans "range"

Bonjour,
Salut David

voir peut être également s'il n'y a pas de cellules fusionnées...

bonne journée
@+

 

[Blafi]

Re : Limite de nombre de cellules dans "range"

Re,
Pendant que j'envoyais ma réponse et mon fichier, je vois qu'il y a eu des messages de Pierrot93 et David84..

Pour répondre à pierrot : il n'y a pas de cellule fusionnée..
Pour le reste, mon fichier joint devrait répondre au questions...

Mais pourquoi ç coince à un moment ? je pense à une limitation du contenu de Range en nombre de cellules mais ai-je raison ??

A ++

 

[Pierrot93]

Re : Limite de nombre de cellules dans "range"

Re,

il me semble que la chaine de caractères dans un "range" ne peut exéder 255 caractères....

 

[Pierrot93]

Re : Limite de nombre de cellules dans "range"

Re,

supprime peut être déjà les $

CellGris = CellGris & "," & c.Address(0,0)

 

[Blafi]

Re : Limite de nombre de cellules dans "range"

Merci Pierrot,

Je pense en effet que la limite doit être de 255 caractères.. je vais déja essayer de supprimer les $ comme tu le dis mais je ne vais pas gagner beaucoup...
Je vais donc me résoudre à sélectionner et copier mes cellules en plusieurs fois..

Merci et à ++

 

[Dranreb]

Re : Limite de nombre de cellules dans "range"

Bonjour.

Qu'est-ce que ça donnerait de déclarer CelGris As Range, et de faire

If CelGris Is Nothing Then Set CelGris = C Else Set CelGris = Union(CelGris, C)

 

[david84]

Re : Limite de nombre de cellules dans "range"

Peut-être comme cela :

Public Sub SelCelGris()
Dim c As Range, UnionC As Range,i as Long
For Each c In Range("C3:C90")
If c.Interior.ThemeColor = xlThemeColorDark2 Then
  i = i + 1
  If i = 1 Then
    Set UnionC = c
  Else
    Set UnionC = Application.Union(UnionC, c)
  End If
End If
Next
Range(UnionC.Address).Select
End Sub

A+

Edit : ou avec Nothing comme proposé par Dranreb (plus logique effectivement) :

Public Sub SelCelGris()
Dim c As Range, UnionC As Range
For Each c In Range("C3:C90")
If c.Interior.ThemeColor = xlThemeColorDark2 Then
  If UnionC Is Nothing Then
    Set UnionC = c
  Else
    Set UnionC = Application.Union(UnionC, c)
  End If
End If
Next
Range(UnionC.Address).Select
End Sub

 

[Blafi]

Re : Limite de nombre de cellules dans "range"

Merci de ces réponses,

Excusez moi mais j'avais du m'absenter et je n'ai lu les solutions qu'aujourd'hui....

Pour répondre à Dranreb et David84, j'ai essayé vos solutions mais le résultat est le même : visiblement quand le nombre d'adresses de cellules dans Range est trop grand, il refuse de fonctionner et renvoie un blocage de la macro..
je me demandais s'il serait possible d'utiliser une matrice pour sélectionner toutes ces cellules, mais là je bloque!
Auriez-vous une solution?
Autrement pour moi, je vais essayer de sélectionner par exemple entre les lignes 2 et 80, lancer ma macro de copie, resélectionner des cellules entre les lignes 81 et 100 par ex, lancer la copie ... et ainsi de suite...
Le problème est que ces cellules ne seront jamais les mêmes et il faut que je reprenne la deuxième sélection là ou j'ai arrété la première et ainsi de suite..
De même, je ne sais pas à l'avance combien de cellules grises seront dans la première plage (ligne 2 à 80) ni dans les autres plages... et il faut que ça marche à tous les coups...(je peux avoir jusqu'à 1500 cellules à explorer sans connaitre à l'avance celles qui seront grises)... Dur, dur!
A +

 

[Guest]

Re : Limite de nombre de cellules dans "range"

Bonjour,

Sur le fichier donnée en post #3, ceci fonctionne très bien.
Code vb:
Public Sub SelCelGris()
'selectionne les cellules grises

Dim c As Range, plg As Range
For Each c In Range("C3:C90")
If c.Interior.ThemeColor = xlThemeColorDark2 Then
If plg Is Nothing Then Set plg = c Else Set plg = Union(c, plg)
End If
Next
If Not plg Is Nothing Then plg.Select

End Sub




Il y avait des restrictions sur le nombre de plage de cellules dans une union jusqu'à la version 2007 de excel mais normalement cette restriction a été levée depuis.

[Edit]
Testé avec succés (quoiqu'un peu long) sur 11364 cellules dans 1882 plages de celllules non adjacentes

Public Sub SelCelGris()
'selectionne les cellules grises

    Dim c As Range, plg As Range
    For Each c In ActiveSheet.UsedRange
        If c.Interior.ThemeColor = xlThemeColorDark2 Then
            If plg Is Nothing Then Set plg = c Else Set plg = Union(c, plg)
        End If
    Next
    If Not plg Is Nothing Then plg.Select

    MsgBox "cellules: " & plg.Count & vbCrLf & "plages:" & plg.Areas.Count
End Sub

A+

 

[Staple1600]

Re : Limite de nombre de cellules dans "range"

Bonjour à tous, (salut Pierrot93, Dranreb, david84, Hasco)

Je vais essayer de faire en plusieurs fois car le but est de copier les lignes entières de ces cellules dans un autre classeur

A tester (notamment la recopie) avec un nombre plus conséquent de cellules matchant le critère

Sub a()
Dim c As Range, plg As Range
For Each c In Range("C3:C90")
If c.Interior.ColorIndex = 15 Then
Cells(c.Row, Columns.Count) = "$$$"
End If
Next
Feuil1.Columns(Columns.Count).SpecialCells(xlCellTypeConstants, 6).EntireRow.Copy Feuil2.[A1]
Feuil1.Columns(Columns.Count).Clear
End Sub

Sub b()
Dim c As Range, plg As Range
For Each c In Feuil1.UsedRange
If c.Interior.ColorIndex = 15 Then
Cells(c.Row, Columns.Count) = "$$$"
End If
Next
Feuil1.Columns(Columns.Count).SpecialCells(xlCellTypeConstants, 6).EntireRow.Copy Feuil2.[A1]
Feuil1.Columns(Columns.Count).Clear
End Sub

NB: ici j'ai testé la recopie sur une autre feuille
Test OK pour les deux macros (avec XL 2003). Mais j'ai testé avec un petit nombre de cellules grises (au sens littéral comme figuré)

EDITION: Question: Cet ajout est-il utile ou pas ? (en terme de vitesse d’exécution du code)

Sub c()
Dim c As Range, plg As Range
For Each c In Range("C3:C90")
If c.Interior.ColorIndex = 15 Then
If IsEmpty(Cells(c.Row, Columns.Count)) Then '<-ajout
Cells(c.Row, Columns.Count) = "$$$"
End If
End If
Next
Feuil1.Columns(Columns.Count).SpecialCells(xlCellTypeConstants, 6).EntireRow.Copy Feuil2.[A1]
Feuil1.Columns(Columns.Count).Clear
End Sub

 

[Si...]

Re : Limite de nombre de cellules dans "range"

Hello

Dis moi Staple, avec ton 2013, tu peux bien filtrer les couleurs non ?
Tu pourras alors tester (très rapide avec mon 2010)

Public Sub SelCelGris()
  Dim P As Range, U As Range
  Set P = Range("C2:C" & ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row)
  P.AutoFilter 1, RGB(238, 236, 225), 8
  Set U = P.SpecialCells(12)
  U.Select
  MsgBox Selection.Address & vbLf & "donc " & U.Count & " cellules"
  P.AutoFilter
End Sub