Discussion :

[amael83]

Bonjour à tous les forum activistes sans qui le VBA serait encore impénétrable pour moi.

D'habitude je trouve toujours de quoi me sortir d'affaire avec toute la ressource disponible sur ce forum, mais là...

Je cherche à alléger le temps de calcul de mon code et cherche à supprimer les ".Select"

Ainsi, pour mettre en forme une plage j'en suis à cette forme de code qui apparement ne tient pas compte du "End(xlDown".
Est ce qu'il est incompatible avec une boucle with ou est ce qu'il y a une subtilité qui m'échappe?

Merci d'avance pour votre aide précieuse.

Le code :

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
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
With Sheets("Synthèse").Range("B12:GE12").End(xlDown)
 
        .Borders(xlDiagonalDown).LineStyle = xlNone
        .Borders(xlDiagonalUp).LineStyle = xlNone
 
        With .Borders(xlEdgeLeft)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThin
        End With
 
        With .Borders(xlEdgeTop)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThin
        End With
 
        With .Borders(xlEdgeBottom)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThin
        End With
 
        With .Borders(xlEdgeRight).LineStyle = xlContinuous
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThin
        End With
 
        With .Borders(xlInsideVertical)
            .LineStyle = xlContinuous
            .ThemeColor = 1
            .TintAndShade = -0.499984740745262
            .Weight = xlThin
        End With
 
        With .Borders(xlInsideHorizontal)
            .LineStyle = xlContinuous
            .ThemeColor = 1
            .TintAndShade = -0.249946592608417
            .Weight = xlThin
        End With
 
    End With

[Philippe Tulliez]

Bonjour,
Effectue ce test et tu comprendras tout suite

Code :

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

1
2
3
4
5
Sub t2()
  With Sheets("Synthèse").Range("B12:GE12").End(xlDown)
   MsgBox .Address
  End With
End Sub

[Filippo]

Bonjour,
pour définir le rectangle sur lequel tu veux faire des opérations il faut compléter ce que tu as fait avec :

Code :

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

With Sheets("Synthèse").Range(Range("B12:GE12"), Range("B12:GE12").End(xlDown))

P.S.
Ma réponse s'est croisée avec celle de Philippe.

[amael83]

Merci pour vos retour croisés, j'ai effectivement utilisé ce code par ailleurs Range(Range("B12:GE12"), Range("B12:GE12").End(xlDown)) pour définir ma plage de sélection mais elle ne semble pas être compatible avec With.

D'ailleurs le code suivant me renvoie un message d'erreur :

Code :

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

1
2
3
With Sheets("Synthèse").Range(Range("B12:GE12"), Range("B12:GE12").End(xlDown))
  MsgBox .Address
End With

[Philippe Tulliez]

Bonjour,
Moi j'utilise toujours la propriété CurrentRegion et je n'ai jamais de problème mais il est vrai que je m'impose une règle de bonne gestion des données et j'oblige mes clients à travailler de la sorte

Code :

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

With Sheets("Synthèse").Range("B12:GE12").CurrentRegion

ou mieux encore mais il faut bien entendu voir si les données sont bien structurées (Colonne B remplie jusqu'à la dernière ligne et la ligne 12 remplie jusqu'à la dernière colonne

Code :

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

With Sheets("Synthèse").Range("B12").CurrentRegion

Cette dernière ligne de code est l'équivalent de faire un Ctrl + * après avoir sélectionné la cellule B12

[Filippo]

Il y a une erreur dans le bloc :

Code :

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

1
2
3
4
5
6
 With .Borders(xlEdgeRight).LineStyle = xlContinuous
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
        End With

Il faut mettre à la place :

Code :

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

1
2
3
4
5
6
        With .Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
        End With

Sinon avec :

Code :

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

With Sheets("Synthèse").Range(Range("B12:GE12"), Range("B12:GE12").End(xlDown))

ou bien

Code :

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

With Sheets("Synthèse").Range("B12:GE12").CurrentRegion

ça fonctionne bien.

[amael83]

Merci à tous les deux pour votre aide mais je n'ai pas réussi à m'en sortir pour autant.

Je n'ai pas pu utilisé

Code :

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

.CurrentRegion

car ma zone ne doit pas être suffisamment bien bornée.

Ni

Code :

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

.Range(Range("B12:GE12"), Range("B12:GE12").End(xlDown))

qui me renvoie une erreur quand il est intégré à une boucle "With".

Du coup je continue pour l'instant avec du code à base de

Code :

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

.Select

avec mes boucle "With"...

[BENNASR]

bonjour
et si tu reviens à ton code initial + la proposition de Filippo
tu remplace :

Code :

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

With Sheets("Synthèse").Range("B12:GE12").End(xlDown)

par

Code :

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

With Sheets("Synthèse").Range("B12:GE" & Range("GE" & Rows.Count).End(xlUp).Row)

et la proposition de FILIPPO :

Il y a une erreur dans le bloc :

Code :

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

1
2
3
4
5
6
 With .Borders(xlEdgeRight).LineStyle = xlContinuous
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
        End With

Il faut mettre à la place :

Code :

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

1
2
3
4
5
6
        With .Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
        End With

ce qui donne

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
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
Sub test()
With Sheets("Synthèse").Range("B12:GE" & Range("GE" & Rows.Count).End(xlUp).Row)
        .Borders(xlDiagonalDown).LineStyle = xlNone
        .Borders(xlDiagonalUp).LineStyle = xlNone
 
        With .Borders(xlEdgeLeft)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThin
        End With
 
        With .Borders(xlEdgeTop)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThin
        End With
 
        With .Borders(xlEdgeBottom)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThin
        End With
 
        With .Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
        End With
 
        With .Borders(xlInsideVertical)
            .LineStyle = xlContinuous
            .ThemeColor = 1
            .TintAndShade = -0.499984740745262
            .Weight = xlThin
        End With
 
        With .Borders(xlInsideHorizontal)
            .LineStyle = xlContinuous
            .ThemeColor = 1
            .TintAndShade = -0.249946592608417
            .Weight = xlThin
        End With
 
    End With
 
End Sub

[MarcelG]

Bonjour,

Quelques remarques:

1 - Je partage cette condition fondamentale évoquée par Philippe, que je salue au passage,

Code :

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

mais il faut bien entendu voir si les données sont bien structurées

Si cette hypothèse est aléatoire alors mieux vaut user de prudence (voir plus bas)

2 - Je trouve cette ligne

Code :

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

.Range(Range("B12:GE12"), Range("B12:GE12").End(xlDown))

un peu lourde et bien entendu génératrice d'erreur.
Mieux vaut, par ailleurs, utiliser End(xlUp)

3 - Bien entendu, bannir ces f... Select

4 - D'autre part, pour information, j'éviterais d'utiliser la méthode SpecialCells, source bien souvent d'erreur.

Proposition:

(dans le cas bien entendu où l'hypothèse de Philippe ne serait pas vérifiée)

Initialiser une variable derlign (Long) à zéro
Considérer la dernière ligne derlign2 colonne par colonne.
Si derlign <derlign2 alors derlign = derlign2
Enfin, intégrer cette variable derlign2 comme argument d'un objet Cells.

Certes, ce processus est pragmatique et "pas très fun" mais il serait, pour le cas présent, efficace.

[OrDonc]

Bonjour
Attention XlDown est défini par la première cellule vide de la colonne !

Pour toute la plage (en fonction de la colonne B)

Code :

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

1
2
3
4
5
6
7
8
Sub bords()
    With Sheets("Synthèse").Range("B12:GE" & Cells(Rows.Count, 2).End(xlUp).Row)  ‘colonne 2 (B)
        .Borders(5).LineStyle = xlNone   'diagonales supprimées si besoin
        .Borders(6).LineStyle = xlNone
        .Borders.LineStyle = 1            'toutes les cellules sont encadrées (couleur standard, taille standard)
        .Borders(12).LineStyle = xlNone  'les lignes intérieures sont supprimées
    End With
End Sub

[MarcelG]

Salut Ordonc,

D'après ce que j'ai compris, les données de notre ami n'ont pas la même dimension.
C'est pourquoi je lui ai proposé de considérer la dernière ligne de chaque colonne.

[patricktoulon]

bonjour a tous

pour commencer
1 currentregion ben NON!!!!c'est pas bon
pourquoi?
et bien parce que currentregion prend en compte toutes les cellules remplies et adjacentes ou s'arrete si il y a plus de 1 cellule vide autour

démonstration:
je réduit pour l'exemple a B:G
on veux donc la région de b12:g12 jusqu'au xldown avec currentregion


mais qué qué's que vient faire la colonne "H" la dedans hein ??????

bon c'est clair pour moi et sans appel : l'utilisation de currentregion n'est pas appropriée qu'on me dise encore le contraire

qu'est qu'on cherche exactement ?
il nous faut donc trouver la derniere ligne utilisée DANS N 'IMPORTE QUELLE COLONNES DE LA PLAGE

FIND , SearchOrder , SearchDirection ca vous parle ?

démonstration :

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
 
Sub testX()
   Dim lig as long 
      With Sheets("Synthèse")
        lig = Columns("B:G").Find(What:="*",  SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        With .Range("B12:G" & lig)
            .Select
            .Borders(xlEdgeLeft).LineStyle = xlContinuous
            .Borders(xlEdgeTop).LineStyle = xlContinuous
            .Borders(xlEdgeRight).LineStyle = xlContinuous
            .Borders(xlEdgeBottom).LineStyle = xlContinuous
            .Borders(xlInsideVertical).LineStyle = xlContinuous
            .Borders(xlInsideHorizontal).LineStyle = xlContinuous
        End With
    End With

vous remarquerez que j'ai enlevé le lookin et/ou le lookat qui dans ce contexte ne sont pas nécessaires puisque qu'on cherche une donnée n'importe la quelle

il n'y a plus qu'a adapter la plage et colonnes pour le ligne "lig=...."

[Philippe Tulliez]

Bonjour Patrick,

pour commencer
1 currentregion ben NON!!!!c'est pas bon
pourquoi?
et bien parce que currentregion prend en compte toutes les cellules remplies et adjacentes ou s'arrete si il y a plus de 1 cellule vide autour

C'est la raison pour laquelle, j'ai bien précisé
"Moi j'utilise toujours la propriété CurrentRegion et je n'ai jamais de problème mais il est vrai que je m'impose une règle de bonne gestion des données et j'oblige mes clients à travailler de la sorte "
+ autre exemple avec explication
"il faut bien entendu voir si les données sont bien structurées (Colonne B remplie jusqu'à la dernière ligne et la ligne 12 remplie jusqu'à la dernière colonne "

+ explication de l'équivalent manuel
"est l'équivalent de faire un Ctrl + * après avoir sélectionné la cellule B12"

Avec cette procédure

Code :

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

1
2
3
4
5
6
7
Sub t()
  Dim rng As Range
  With ThisWorkbook.Worksheets("Consult")
  Set rng = .Range("B12").CurrentRegion
  End With
  Debug.Print rng.Address
End Sub

rng = Objet Range représentant la plage $B$12:$G$21

[OrDonc]

Re

Oui Marcel (salut), je n’avais lu ton message quand j'ai posté .
J’ai bien précisé que je prenais en compte la colonne B.
Ma proposition peut servir dans le cas où les colonnes ont des longueurs différentes, en y intégrant une boucle (dans la fenêtre des codes de l’onglet source) :

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 bords()
    Dim n As Integer, dl As Long
    For n = 2 To [GE1].Column
        dl = Cells(Rows.Count, n).End(xlUp).Row
        If dl > 11 Then ‘colonne non vide
            With Cells(12, n).Resize(dl - 11)
                .Borders(5).LineStyle =
                .Borders(6).LineStyle = xlNone
                .Borders.LineStyle = 1
                .Borders(12).LineStyle = xlNone 
            End With
        End If
    Next
End Sub

Maintenant, on peut compliquer les choses à souhait mais ce n’est pas ma tasse de thé.

Nota : j’ai déjà signalé plusieurs fois que

Code :

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

dl = Cells(Rows.Count, n).End(xlUp).Row

pouvait poser problème ! Il en est de même avec

Code :

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

dl = Columns(n).Find("*", SearchOrder:=1, SearchDirection

(colonne vidée)

[patricktoulon]

re

"il faut bien entendu voir si les données sont bien structurées (Colonne B remplie jusqu'à la dernière ligne et la ligne 12 remplie jusqu'à la dernière colonne "

bonsoir Philippe même si sur le fond de toute façon je ne peux qu'être d'accords avec toi , reste que sauf organisation des données, voir une colonne vide avant et après avec currentregion tu risque de prendre les adjacentes, c'est un débat qui a duré ca aussi je retrouve plus la discussion
j'en ai retenu la leçon a mes dépends

donc non je ne suis pas d'accords avec toi sur le fait que currentregion et la méthode la plus sure loin............ de la
la plus part du temps ca implique un tableau par feuille c'est ce qu'il en ressort de beaucoup d'utilisateurs

non la question est :quelle est la dernière ligne utilisée de la plage currentregion plouff!!!( et entre nous il est pas fait pour ca )

d'ailleurs sans vouloir te vexer c'est celui que j'utilise le moins
on ne métrise rien avec ni limite-off ni limite-in rien du tout il te donne ce qu'il prends et basta


alors existe il l'outils je dis oui
la méthode find xlprevious est justement fait pour ca (pourquoi s'en priver)

après comme Marcel l'a suggéré on peut examiner colonne par colonne sur le rows.count (xlup)mais c'est mouliner pour rien alors que l'on a déjà l'outils qui fonctionne très bien
qui est accessible même au débutants
c'est facile a comprendre xlprevious/xlnext l'un doit partir d'en bas l'autre d'en haut et basta

Colonne B remplie jusqu'à la dernière ligne et la ligne 12 remplie jusqu'à la dernière colonne "

ouais ben si il y a des cellules vides par ce qu'elle doivent le rester selon un calcul ou autre ben t'es chocolat

c'est bon pour le stress le chocolat il parait

[clementmarcotte]

Moi, ce que j'en dis, c'est que la meilleure façon de s'assurer que l'on sélectionne la bonne plage, c'est le contrôle RefEdit.

Parce que, comme c'est si bien dit, aucune des méthodes existantes n'est vraiment parfaite; sauf si on sélectionne une plage soigneusement construite et sans intervention externe.


Et surtout qu'avec le copier-coller, il y a encore de ces trucs qui sont programmés avec "A65536"

[amael83]

Merci à tous pour vos retours qui suscitent bien des débats mais dont les nuances me dépassent
Bref j'ai repris mon travail de simplification de code et j'ai commencé par essayer la solution de patricktoulon parce que je suis du var moi aussi
Ca fonctionne parfaitement ! mais je suis sûr que d'autres de vos solutions auraient pu tout aussi bien fonctionner.
Je vais la déployer dans le reste de mon code pour voir les bénéfices sur le temps de calcul.
Cela dit, il me reste une petite question qui me turlupine, il reste tout de même un

Code :

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

.select

qui traîne dans le code que tu me proposes.
Je le garde?

[patricktoulon]

re
bonsoir
non je l'ai mis juste pour te montrer que la plage sélectionnée est bien déterminée tu peux virer
tu es d'où dans le var?

[Pierre Fauconnier]

Salut.

Ceci juste pour dire que Cells(Rows.Count, 2).End(xlUp) peut provoquer une erreur à l'exécution. Perso, j'utilise sans complexe Cells(1048576, 2).End(xlUp). Je pense qu'avant qu'on ajoute des lignes à une feuille Excel, il faudra attendre quelques versions. Et qu'on ne me parle pas de compatibilité descendante, ça n'a aucun sens

Si vous voulez vraiment "prévoir" qu'Excel aura plus de lignes dans une prochaine version et que vous imaginez que la solution Excel proposée aujourd'hui fonctionnera toujours à ce moment-là ( ), utilisez alors worksheets(1).rows.Count. Dans la mesure où l'on est à peu près certain qu'il y aura au moins une feuille de calcul dans le classeur, ça passera, sauf cas d'école exceptionnel et tiré par les cheveux. Ici, on propose un code censé fonctionner pour les versions futures mais qui peut planter dans l'actuelle (et dans les futures aussi, d'ailleurs). Cela n'a guère de sens, de mon point de vue en tout cas

Moi, ce que j'en dis, c'est que la meilleure façon de s'assurer que l'on sélectionne la bonne plage, c'est le contrôle RefEdit.[...]

Bof.. Interrompre une macro pour demander à l'utilisateur de spécifier sa plage?

[amael83]

@ patricktoulon : de Vinon sur Verdon