Sélectionner toutes les cellules non vides pour créer graphique
Bonjour à tous,
je suis débutant en VBA alors je m'aide des macros à enregistrer...
Voici mon pb.
J'ai un tableau assez complet (beaucoup de lignes et colonnes) sur une bases d'élèves.
Je voudrais sélectionner toutes les cellules d'une colonne mais m'arrêter à la dernière cellule non vide.
Puis, avec cette sélection, créer sur une nouvelle page un tableau croisé dynamique.
voici la formule :
DerniereLigne = Range("J65536").End(xlUp).Row
End With
Donc au début, je définis ma sélection (en gros : de la ligne 2 de la colonne J jusqu'à la dernière cellule de la colonne)
et ça ça fonctionne.
Par contre, après (ligne 5) : il faut que je définisse une plage (Sheet1!R1C10:R300C10) or je voudrais justement qu'il utilise ma sélection !
Une petite aide ? astuce ? solution ?
Merci 1000 fois.
Laurent.
A quoi sert
Range("J2:J" & DerniereLigne).Select
dans ton code?
Edit: j'ai mal lu. Là ça sert a rien ta selection, il faut définir une variable range qui va contenir ta selection et que tu places dans ton code
Bonjour (et merci de t'intéresser à mon pb).
avec Range("J2:J" & DerniereLigne).Select
je souhaite justement définir une sélection (celle dont je vais avoir besoin..)
Mais effectivement, après elle ne me sert pas (ce qui m'embête...)
Merci !
A tester (comme je n'ai pas de fichier je ne peux pas tester en réel):
DerniereLigne = ActiveSheet.Range("J65536").End(xlUp).Row
Set monrange = ActiveSheet.Range("J2:J" & DerniereLigne)
Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
monrange, Version:=6).CreatePivotTable TableDestination:= _
"Feuil1!R1C1", TableName:="Tableau croisé dynamique13", DefaultVersion:=6
Sheets("Feuil1").Select
ActiveSheet.Shapes.AddChart2(201, xlColumnClustered).Select
ActiveChart.SetSourceData Source:=Range("Feuil1!$A$1:$C$18")
ActiveChart.PivotLayout.PivotTable.AddDataField ActiveChart.PivotLayout. _
PivotTable.PivotFields("codage pathologie"), "Nombre de codage pathologie", _
xlCount
With ActiveChart.PivotLayout.PivotTable.PivotFields("codage pathologie")
.Orientation = xlColumnField
.Position = 1
End With
Après les TCD avec l'enregistreur ça fait pas super bon ménage
Si ça marche pas on testera autre chose
Re,
je viens de tester, il me met "Erreur d'exécution "1004"
"La méthode "PivotFields" de l'objet "PivotTable" a échoué...
Et il me surligne en jaune :
ActiveChart.PivotLayout.PivotTable.AddDataField ActiveChart.PivotLayout. _
PivotTable.PivotFields("codage pathologie"), "Nombre de codage pathologie", _
xlCount
Il faudrait un fichier.
C'est un peu compliqué sans.
Au moins un fichier avec des valeurs bidon qui reprennent les mêmes feuilles et colonnes
ça marche,
je vais anonymer mon tableau et je vous l'envoie...
Merci !
Voici en PJ mon tableau
et la macro suivante :
Sub mise_en_forme_tableau_total_eleves()
'
' mise_en_forme_tableau_total_eleves Macro
'
'selectionner les colonnes et mettre en forme les couleurs
Columns("A:AZ").Select
Selection.Columns.AutoFit
Columns("A:J").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent4
.TintAndShade = 0.599993896298105
.PatternTintAndShade = 0
End With
Columns("K:S").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent6
.TintAndShade = 0.599993896298105
.PatternTintAndShade = 0
End With
Columns("T:AL").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent1
.TintAndShade = 0.599993896298105
.PatternTintAndShade = 0
End With
Columns("AM:AV").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent2
.TintAndShade = 0.599993896298105
.PatternTintAndShade = 0
End With
Columns("AW:AY").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 7929343
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Columns("AZ:AZ").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.149998474074526
.PatternTintAndShade = 0
End With
Cells.Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
Columns("A:AZ").Select
Selection.AutoFilter
'ajout colonne etbs
Columns("BC:BC").Select
Selection.Copy
Columns("M:M").Select
Selection.Insert Shift:=xlToRight
Range("M1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "etbs"
Range("M2").Select
ActiveCell.FormulaR1C1 = _
"=IF(OR(RC[-2]=""TERM"",RC[-2]=""2NDE"",RC[-2]=""1ERE"",RC[-2]=""CAP BEP"",RC[-2]=""BAC PRO"",RC[-2]=""BAC TECH""),""lycéen"",IF(OR(RC[-2]=""CP (c1)"",RC[-2]=""CE1 (c2)"",RC[-2]=""CE2 (c2)"",RC[-2]=""CM1 (c3)"",RC[-2]=""CM2 (c3)""),""écolier"",""collégien""))"
Range("M2").Select
Selection.AutoFill Destination:=Range("M2:M300"), Type:=xlFillDefault
Range("M1").Select
Selection.Font.Bold = True
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns("M:M").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent6
.TintAndShade = 0.599993896298105
.PatternTintAndShade = 0
End With
'graphique pathologies
DerniereLigne = Range("J65536").End(xlUp).Row
Range("J2:J" & DerniereLigne).Select
Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Sheet1!R1C10:R300C10", Version:=6).CreatePivotTable TableDestination:= _
"Feuil1!R1C1", TableName:="Tableau croisé dynamique15", DefaultVersion:=6
Sheets("Feuil1").Select
Cells(1, 1).Select
ActiveSheet.Shapes.AddChart2(201, xlColumnClustered).Select
ActiveChart.SetSourceData Source:=Range("Feuil1!$A$1:$C$18")
ActiveChart.PivotLayout.PivotTable.AddDataField ActiveChart.PivotLayout. _
PivotTable.PivotFields("codage pathologie"), "Nombre de codage pathologie", xlCount
With ActiveChart.PivotLayout.PivotTable.PivotFields("codage pathologie")
.Orientation = xlColumnField
.Position = 1
End With
Sheets("Feuil1").Select
Sheets("Feuil1").Name = "pathologies"
Sheets("Sheet1").Select
Range("B2").Select
' supprimer les lignes en trop
ActiveSheet.Cells(Rows.Count, "A").End(xlUp)(2).Select
ActiveCell.EntireRow.Select
Dim Compteur As Long
For Compteur = 1 To 200
Selection.Delete Shift:=xlUp
Next Compteur
'graphique répartition par type d'établissements
DerniereLigne = Range("M65536").End(xlUp).Row
Range("M2:M" & DerniereLigne).Select
Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Sheet1!R1C13:R300C13", Version:=6).CreatePivotTable TableDestination:= _
"Feuil2!R1C1", TableName:="Tableau croisé dynamique16", DefaultVersion:=6
Sheets("Feuil2").Select
Cells(1, 1).Select
ActiveSheet.Shapes.AddChart2(201, xlColumnClustered).Select
ActiveChart.SetSourceData Source:=Range("Feuil2!$A$1:$C$18")
ActiveChart.PivotLayout.PivotTable.AddDataField ActiveChart.PivotLayout. _
PivotTable.PivotFields("etbs"), "Nombre de etbs", xlCount
With ActiveChart.PivotLayout.PivotTable.PivotFields("etbs")
.Orientation = xlColumnField
.Position = 1
End With
ActiveChart.ChartType = xl3DPie
With ActiveChart.PivotLayout.PivotTable.PivotFields("etbs")
.Orientation = xlRowField
.Position = 1
End With
ActiveChart.ClearToMatchStyle
ActiveChart.ChartStyle = 264
ActiveChart.ChartTitle.Select
ActiveChart.ChartTitle.Text = "répartition / niveau d'établissements"
Range("P14").Select
Sheets("Feuil2").Select
Sheets("Feuil2").Name = "établissements"
Sheets("Sheet1").Select
Range("C2").Select
' graphique répartition par sexe
Range("G2:G119").Select
Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Sheet1!R1C7:R300C7", Version:=6).CreatePivotTable TableDestination:= _
"Feuil3!R1C1", TableName:="Tableau croisé dynamique17", DefaultVersion:=6
Sheets("Feuil3").Select
Cells(1, 1).Select
ActiveSheet.Shapes.AddChart2(201, xlColumnClustered).Select
ActiveChart.SetSourceData Source:=Range("Feuil3!$A$1:$C$18")
ActiveChart.PivotLayout.PivotTable.AddDataField ActiveChart.PivotLayout. _
PivotTable.PivotFields("Sexe"), "Nombre de Sexe", xlCount
With ActiveChart.PivotLayout.PivotTable.PivotFields("Sexe")
.Orientation = xlColumnField
.Position = 1
End With
ActiveChart.ChartType = xl3DPie
With ActiveChart.PivotLayout.PivotTable.PivotFields("Sexe")
.Orientation = xlRowField
.Position = 1
End With
ActiveChart.ClearToMatchStyle
ActiveChart.ChartStyle = 264
ActiveChart.ChartTitle.Select
ActiveChart.ChartTitle.Text = "Répartition / sexe"
Range("L6").Select
Sheets("Feuil3").Select
Sheets("Feuil3").Name = "sexe"
Range("J26").Select
Sheets("Sheet1").Select
Range("A2").Select
End Sub
Quel est l'interêt de cette partie du code:
' supprimer les lignes en trop
ActiveSheet.Cells(Rows.Count, "A").End(xlUp)(2).Select
ActiveCell.EntireRow.Select
Eh eh, ça c'est de la magouille...
Dans la partie ' ajout colonne établissement', je trie les données de la classe :
Si l'élève est en CP-CE1-CE2-CM1 ou CM2 : il indique : écolier
s'il est en term 1ère 2nde ou Bac pro : il indique lycéen,
sinon il indique collégien.
Mais pour faire cela (comme je ne sais pas combien j'ai d'élèves), il me le fait pour les 300 premières lignes (largement suffisant)
Du coup, ensuite j'utilise cette "fonction magouille" pour supprimer les lignes en trop...
Il doit y avoir plus simple, c'est sûr
OK du coup comme j'ai modifié l'incrémentation de la formule pour qu'elle se cantonne a la dernioère ligne non vide du tableau je retire cette partie
Voilà le résultat
oh là là là là !!!! Et en plus ça avait l'air simple
Je vais regarder ça de près et étudier cette macro pour continuer à la développer...
Merci 10000 fois !!!
A bientôt (pour mon prochain pb
Laurent.
Bonjour bonjour !
j'ai encore un petit souci...
J'essaye de faire un tableau (sur une nouvelle feuille) pour croiser les données...
Par exemple, savoir combien j'ai de personnes qui sont à la fois des garçons et des écoliers ?
Voici ma formule :
'page de calculs
"= COUNTIFS(Sheet1!R[1]C[5]:R[derniere_ligne]C[5],""Garçon"",Sheet1!R[1]C[11]:R[derniere_ligne]C[11],""écolier"")"
Mais cela ne fonctionne pas (depuis que j'ai mis "derniere_ligne"...)
Si vous avez des idées !!!
Par ailleurs, la référence à une cellule (FormulaR1C1) fait que je dois tout décaler en fonction de la cellule dans laquelle le résultat sera écrit... Pfffff...
Merci et belle journée.
Laurent
ActiveCell.FormulaR1C1 = _
"= COUNTIFS(Sheet1!R[1]C[5]:R[derniere_ligne]C[5],""Garçon"",Sheet1!R[1]C[11]:R[" & derniere_ligne & "]C[11],""écolier"")"
Merci, mais malheureusement... j'ai l'erreur d'exécution 1004 :
La méthode "FormulaR1C1 de l'objet range a échoué...
Voici ce que j'ai mis :
'page de calculs
"= COUNTIFS(Sheet1!R[1]C[5]:R[derniere_ligne]C[5],""Garçon"",Sheet1!R[1]C[11]:R[" & derniere_ligne & "]C[11],""écolier"")"
new_sheet.Name = "calculs"
Si tu as une autre idée
Merci en tout cas...
oups !!! ça fonctionne !!!
J'ai ajouté aussi & et " à la première partie de la formule...
MERCI !!!
A très bientôt pour de nouvelles aventures
Laurent.
Bonjour !
Et oui, encore moi
Petite question (j'imagine toute simple...).
J'ai cette formule :
Cells(2, 1).Value = "écolières"
Range("B2").Select
ActiveCell.FormulaR1C1 = _
"= COUNTIFS(Sheet1!R[0]C[5]:R[" & derniere_ligne & "]C[5],""Fille"",Sheet1!R[0]C[11]:R[" & derniere_ligne & "]C[11],""écolier"")"
Mais à la place de la dernière ligne (derniere_ligne), je voudrais avoir la ligne précédente... ?
J'ai bien sûr tenté avec derniere_ligne -1 mais ça ne fonctionne pas...
Une petite idée ?
Merci !
Laurent.
ActiveCell.FormulaR1C1 = _
"= COUNTIFS(Sheet1!R[0]C[5]:R[" & derniere_ligne - 1 & "]C[5],""Fille"",Sheet1!R[0]C[11]:R[" & derniere_ligne - 1 & "]C[11],""écolier"")"
Ca devrait fonctionner
Merci !!!
C'est parfait, je n'avais pas mis le -1 au bon endroit...
Bonne journée.
Laurent.