Discussion :

[Aliocha7775]

Hello Le Forum,

Bonjour à tous,

J'ai un problème que je veux exécuter une macro pour faire un TCD avec une plage de données variables.

Je vous expose mon problème en "Feuil2" j'ai un tableau qui commence en cellule A1 qui peut avoir un nombre de lignes et colonnes variable.

Je veux faire Mon TCD en "Feuil3" en sélectionnant cette plage de données variables.

Voici le code ci-dessous ou il me renvoit une erreur d'éxécution ("1004" pour une référence non valide) pour les lignes marquées en gras :

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
Sub Bouton6_Cliquer()

'Déclaration des variables
Dim maPlage As Range, Ligfin As Long, Colfin As Long

'Attribution des valeurs
Ligfin = Range("A1048576").End(xlUp).Row ' ou Ligfin = Cells(Rows.Count, 1).End(xlUp).Row
Colfin = Range("XFD1").End(xlToLeft).Column ' ou Colfin = Cells(1, Cells.Columns.Count).End(xlToLeft).Column
 
'définition de la plage de données variable
With Worksheets("Feuil2").Activate
Set maPlage = Sheets("Feuil2").Range(Cells(1, 1), Cells(Ligfin, Colfin))
End With
     

    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "maPlage", Version:=6).CreatePivotTable TableDestination:= _
        "Feuil3!R3C1", TableName:="Tableau croisé dynamique5", DefaultVersion:=6
   Sheets("Feuil3").Cells(3, 1).Select


End Sub

Est-ce que quelqu'un aurait un idée sur ce qui ne pas car je suis à cours de solution après avoir fait le tour sur différents forums.

Merci d'avance pour votre aide.

Cdlt.

Aliocha7775

[Overeath]

Bonjour,
Au vu de ton problème, je te propose cette façon d'opérer qui est quasiment la même mais qui permet à ta macro d'avoir un temps d’exécution plus faible :

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
 
Sub selec()
 
Dim y, x As Long, plage As range
 
While Cells(y, 1) <> ""
    y = y + 1
Wend
 
While Cells(1, x) <> ""
    x = x + 1
Wend
 
y = y - 1
x = x - 1
 
range(Cells(1, 1), Cells(y, x)).Select
 
End Sub

Il prend simplement toutes les lignes jusqu'à ce qu'il n'y ait rien dans la cellule d’après, pareil pour les colonnes puis une fois qu'il a les coordonnées de la cellule la plus éloignée de A1, il sélectionne la plage
J'espère t'avoir aidé et n'hésite pas à me le dire si ça ne répond pas à ton problème ^^

[riaolle]

Bonjour,
@Overeath : es-tu sûr que le temps d'exécution est plus court? Normalement, quand on cherche la dernière ligne non vide d'une feuille on utilise :

Code :

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

derLig = Cells(Rows.Count, 1).End(xlUp).Row

@Aliocha7775 :

1. As-tu bien compris la syntaxe du bloc With...End With? As-tu bien compris à quoi cela sert?
   Ceci :
   

   Code :

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

   1
   2
   3
   With Worksheets("Feuil2").Activate
       Set maPlage = Sheets("Feuil2").Range(Cells(1, 1), Cells(Ligfin, Colfin))
   End With

   n'est pas correct et surtout tu n'utilises pas la "fonctionnalité" du bloc With. Activate rend la feuille active. Le bloc With sert à "fixer" un objet sur lequel on veut travailler pour ne pas devoir refaire référence à cette objet. Ex .:
   

   Code :

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

   1
   2
   3
   4
   5
   6
   With Sheets("ma feuille")
       .Range("A1").Value = "toto"
       .Range("A2").Value = "titi"
       .Range("A3").Value = "tutu"
       .Range("A4").Value = "tata"
   End With

   est équivalent à
   

   Code :

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

   1
   2
   3
   4
   Sheets("ma feuille").Range("A1").Value = "toto"
   Sheets("ma feuille")..Range("A2").Value = "titi"
   Sheets("ma feuille")..Range("A3").Value = "tutu"
   Sheets("ma feuille")..Range("A4").Value = "tata"

   Tu vois donc que cela permet de ne pas devoir répéter Sheets("ma feuille") plusieurs fois. Attention à bien utiliser les points pour faire référence à l'objet du bloc With !!!
   Dans ton cas, tu devrais écrire :
   

   Code :

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

   1
   2
   3
   With Worksheets("Feuil2")
       Set maPlage = .Range(Cells(1, 1), Cells(Ligfin, Colfin))
   End With

   Mais ici, le bloc with complique les choses plutôt que de les simplifier. Ecris plutôt :
   

   Code :

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

   Set maPlage = Sheets("Feuil2").Range(Cells(1, 1), Cells(Ligfin, Colfin))

2. Par rapport à l'erreur soulevée :
   Attention, tu confonds les variables/objets et le texte représentant la variable/l'objet. Petit exemple :
   

   Code :

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

   1
   2
   3
   4
   Dim maVariable As String
   maVariable = "ceci est du texte"
   MsgBox maVariable         'va afficher le texte : "ceci est du texte"
   MsgBox "maVariable"      'va afficher le texte : 'maVariable"

   Quand tu écris :

   Code :

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

   SourceData:="maPlage"

   tu dis que ta source est le texte "maPlage", or toi tu veux que ta source soit l'objet maPlage. Ecris donc :
   

   Code :

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

   1
   2
   3
   ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
    maPlage, Version:=6).CreatePivotTable TableDestination:= _
    "Feuil3!R3C1", TableName:="Tableau croisé dynamique5", DefaultVersion:=6

[Overeath]

Bonjour Riaolle,

derLig = Cells(Rows.Count, 1).End(xlUp).Row

Effectivement cette ligne est bien bien plus rapide mais je voulais surtout comparer ce que j'ai écris avec

Ligfin = Range("A1048576").End(xlUp).Row

qui me paraissait très étrange.
Cependant je n'ai aucune idée de la manière d'opérer de cette fonction si ce n'est qu'elle balaye les cellules à la recherche de la fin, mais tu as surement raison
Cordialement,
Un aspirant à la programmation

[Aliocha7775]

Re boujour à tous et merci à vous pour vos réponses !;-)

Après quelques tests voici le code qui marche bien mais qui reste optimisable :

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
Sub Bouton6_Cliquer()
 
'suppression TCD ancienne version
    Sheets("Feuil3").Select
    Cells.Select
    Selection.Delete Shift:=xlUp
 
 
'Création TCD avec plage de données dynamique
 
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        ThisWorkbook.Worksheets("Feuil2").Range("A1").CurrentRegion, Version:=xlPivotTableVersion14).CreatePivotTable _
        TableDestination:="Feuil3!R3C1", TableName:="Tableau croisé dynamique6", _
        DefaultVersion:=xlPivotTableVersion14
 
End Sub

J'ai essayé de mettre mettre sur une ligne :

Code :

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

1
2
3
   Sheets("Feuil3").Select
    Cells.Select 
    Selection.Delete Shift:=xlUp

et de le remplacé par

Code :

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

1
2
    Sheets("Feuil3").Cells.Select
    Selection.Delete Shift:=xlUp

Mais cela ne marche et renvoi une erreur "1004 - La méthode de sélection de l'objet range a échoué". Visiblement je n'ai écris correctement mais je ne vois pas comment faire après plusieurs test.

De mon côté maintenant c'est bon et merci encore.

Cdlt.

Aliocha7775.

[riaolle]

Tu peux aller encore plus loin :

Code :

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

Sheets("Feuil3").Cells.Delete shift:=xlUp

Il faut éviter d'utiliser Select/Selection/Activate/ActiveCell et leurs copains.

[Invité]

https://www.developpez.net/forums/d1...e/#post7916923