Selectioner un tableau de taille variable

Le 31/08/2017 à 10:34

Bonjour,

Chaque semaine je dois importer un tableau de données d'une taille différente. Ce que j'aimerais c'est que ma variable "plage" contienne toutes les cellules de mon tableau.

Par défaut j'ai mis Set plage = Range("A1:Z200") , mais ça me pose des problèmes par la suite.

J' ai trouver la formule

ActiveSheet.UsedRange.Select ,le problème c'est qu’après j'ai une boucle For Each cel In plage

Dans un second temps j'aimerais pouvoir sélectionner que certaines parties de mon tableau

ex ma colonne H uniquement ou retirer la première ligne de mon tableau.

Ce qui reviendrai a quelque chose du Genre Range ("H1:HX")

où X est mon nombre de ligne.

Ou Range("A2:ZX")

Merci par avance.

Le 31/08/2017 à 10:52

Bonjour,

Merci de joindre ton fichier pour un conseil efficace.

Le 31/08/2017 à 12:22

J'ai dût modifier le fichier car il contenais des données personnels. Les XXXXX peuvent être des numéros du texte ou des cellules vide.

Si vous voyer des chose a amélioré je suis preneur.

Sub Experimental()
'
' Experimental Macro
'
Dim x As Integer
    x = 1

Dim CAMP As Integer
CAMP = 0

Dim CMN As Integer
CMN = 0

Dim cel

Dim cel2

Dim plage As Range

Dim NbStagiaireTotal As Integer

'Stastistique et analyse de données

    'Renomer la feuil1

 Worksheets("Stagiaire").Name = "STAGIAIRE CMN"

    'compte le nombre de stagiaire

    NbStagiaireTotal = Cells.SpecialCells(xlCellTypeLastCell).Row
    NbStagiaireTotal = (NbStagiaireTotal - 1) 'on enléve une ligne car la première ligne contien les titre de colone

    Set plage = Range("E2:E200")

For Each cel In plage

    If cel.Value Like "*CAMP*" Then
   CAMP = CAMP + 1
   Else
   CMN = CMN + 1

    End If
    Next cel

 MsgBox "Nombre de stagiaire : " & NbStagiaireTotal & Chr(10) & "Nombre de CMN : " & CMN & Chr(10) & "Nombre de Camp : " & CAMP

'Traitement des données

    'Supression des colones inutiles

    Range("A1,C1,D1,F1,G1,H1,M1,N1,O1,Q1:U1,X1:AB1,AD1,AE1,AG1:AZ1").Select
    Selection.EntireColumn.Delete

    'tri par ordre alphabetique "stage" sous niveau "nom"

    ActiveWorkbook.Worksheets("STAGIAIRE CMN").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("STAGIAIRE CMN").Sort.SortFields.Add Key:=Range( _
        "B3:B79"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    ActiveWorkbook.Worksheets("STAGIAIRE CMN").Sort.SortFields.Add Key:=Range( _
        "C3:C79"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("STAGIAIRE CMN").Sort
        .SetRange Range("A1:K180")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

    ' suprimer le baground bleu

   Set plage = Range("A1:I200")

    For Each cel In plage
     cel.Interior.ColorIndex = xlColorIndexNone

    Next cel

    ' colorer les cellules en fonction du stage

Set plage = Range("B1:B200")

For Each cel In plage

    If cel.Value Like "*100*" & "*%*" & "*BARCARES*" Then
        cel.Interior.ColorIndex = 46

    ElseIf cel.Value Like "*FULL*" & "*KITESURF*" Then
        cel.Interior.ColorIndex = 6

    ElseIf cel.Value Like "*FULL*" & "*WIND*" Then
        cel.Interior.ColorIndex = 41

    ElseIf cel.Value Like "*WIND*" & "WAKE" & "*KITE*" Then
        cel.Interior.ColorIndex = 10

    ElseIf cel.Value Like "*KITE*" & "*WAKE*" Then
        cel.Interior.ColorIndex = 6

    ElseIf cel.Value Like "*PASS*" & "*MULTI*" & "*BARCARES*" Then
        cel.Interior.ColorIndex = 26

    ElseIf cel.Value Like "*SEJOUR*" Then
        cel.Interior.ColorIndex = 38

    End If

Next cel

    ' Arivé anticiper transformé les oui non en surf

    Set plage = Range("H2:I200")

For Each cel2 In plage

    If cel2.Value Like "*OUI*" Then
    cel2.FormulaR1C1 = "Surf"
      cel2.Interior.ColorIndex = 3
     Else
   cel2.FormulaR1C1 = ""

    End If
    Next cel2

'Mise en page

    'largeur et hauteur colone et ligne

    Columns("A:A").Select
    Selection.ColumnWidth = 7.71

    Columns("B:B").Select
    Selection.ColumnWidth = 16

    Columns("C:C").Select
    Selection.ColumnWidth = 13.14

    Columns("D:D").Select
    Selection.ColumnWidth = 11.71

     Columns("E:E").Select
    Selection.ColumnWidth = 4.71

    Columns("F:F").Select
    Selection.ColumnWidth = 2.14

    Columns("G:G").Select
    Selection.ColumnWidth = 2.71

    Columns("H:K").Select
    Selection.ColumnWidth = 4.29

    Columns("L:L").Select
    Selection.ColumnWidth = 38.86

     Rows("1:180").Select
    Selection.RowHeight = 20

    ' Renvoie automatique a la ligne

    Rows("1:1").Select
    Selection.RowHeight = 25

      With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With

    ' Créé la fiche "SAGIARE CAMP"

    Sheets.Add After:=Sheets(Sheets.Count)
    Sheets("Feuil1").Select
    Sheets("Feuil1").Name = "STAGIAIRE CAMP"

    'copier la liste complete des stagiaires

   Sheets("STAGIAIRE CMN").Select
     Range("A1:K200").Select
    Selection.Copy
    Sheets("STAGIAIRE CAMP").Select
    Range("A1").Select
    ActiveSheet.Paste

    ' ligne et colone feuille Stagiaire CAmp

    Columns("A:A").Select
    Selection.ColumnWidth = 7.71

    Columns("B:B").Select
    Selection.ColumnWidth = 16

    Columns("C:C").Select
    Selection.ColumnWidth = 13.14

    Columns("D:D").Select
    Selection.ColumnWidth = 11.71

     Columns("E:E").Select
    Selection.ColumnWidth = 4.71

    Columns("F:F").Select
    Selection.ColumnWidth = 2.14

    Columns("G:G").Select
    Selection.ColumnWidth = 2.71

    Columns("H:K").Select
    Selection.ColumnWidth = 4.29

    Columns("L:L").Select
    Selection.ColumnWidth = 38.86

     Rows("2:180").Select
    Selection.RowHeight = 20

    Rows("1:1").Select
    Selection.RowHeight = 25

    'appliquer les filtre CAMP et NON CAMP

    Sheets("STAGIAIRE CAMP").Select
    ActiveSheet.Range("$A$1:$G$2").AutoFilter Field:=2, Criteria1:="=*CAMP*", _
        Operator:=xlAnd

        Sheets("STAGIAIRE CMN").Select
        Range("A1:G200").Select
    ActiveSheet.Range("$A$1:$G$180").AutoFilter Field:=2, Criteria1:="<>*CAMP*" _
        , Operator:=xlAnd

    ' Affichage stastistique

 Sheets.Add After:=Sheets(Sheets.Count)
    Sheets("Feuil2").Select
    Sheets("Feuil2").Name = "STATISTIQUE"

     Columns("A:A").Select
     Selection.ColumnWidth = 16

     Range("A2").Select
     ActiveCell.FormulaR1C1 = "Nb stagiaire"

     Range("A3").Select
     ActiveCell.FormulaR1C1 = "Nb Stagiaire CAMP"

     Range("A4").Select
     ActiveCell.FormulaR1C1 = "Nb Stagiaire CMN"

     Range("B2").Select
     ActiveCell.FormulaR1C1 = NbStagiaireTotal

     Range("B3").Select
     ActiveCell.FormulaR1C1 = CAMP

    ' mettre les titre
    'titre feuille CMN

 Sheets("STAGIAIRE CMN").Select
Rows("1:1").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "CMN"
    Range("A1:L1").Select
    Range("G1").Activate
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.Merge
    Selection.Font.Bold = True
    With Selection.Font
        .Name = "Arial"
        .Size = 48
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
    End With

    'titre feuille CAMP

  Sheets("STAGIAIRE CAMP").Select
 Rows("1:1").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "CAMP"
    Range("A1:L1").Select
    Range("G1").Activate
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.Merge
    Selection.Font.Bold = True
    With Selection.Font
        .Name = "Arial"
        .Size = 48
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
    End With

     'Metre le titre de la colone L -chambre-

Sheets("STAGIAIRE CMN").Select
     Range("L3").Select
     ActiveCell.FormulaR1C1 = "Chambre"

     Sheets("STAGIAIRE CAMP").Select
      Range("L2").Select
     ActiveCell.FormulaR1C1 = "Chambre"

     'Mise en page avant impretion CMN

    Sheets("STAGIAIRE CMN").Select

      With ActiveSheet.PageSetup
        .PrintTitleRows = ""
        .PrintTitleColumns = ""
    End With
    ActiveSheet.PageSetup.PrintArea = ""
    With ActiveSheet.PageSetup
        .LeftHeader = ""
        .CenterHeader = ""
        .RightHeader = ""
        .LeftFooter = ""
        .CenterFooter = ""
        .RightFooter = ""
        .LeftMargin = Application.InchesToPoints(0.787401575)
        .RightMargin = Application.InchesToPoints(0.787401575)
        .TopMargin = Application.InchesToPoints(0.984251969)
        .BottomMargin = Application.InchesToPoints(0.984251969)
        .HeaderMargin = Application.InchesToPoints(0.5)
        .FooterMargin = Application.InchesToPoints(0.5)
        .PrintHeadings = False
        .PrintGridlines = False
        .PrintComments = xlPrintNoComments
        .CenterHorizontally = False
        .CenterVertically = False
        .Orientation = xlLandscape
        .Draft = False
        .PaperSize = xlPaperLetter
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = 100
        .PrintErrors = xlPrintErrorsDisplayed
        .OddAndEvenPagesHeaderFooter = False
        .DifferentFirstPageHeaderFooter = False
        .ScaleWithDocHeaderFooter = True
        .AlignMarginsHeaderFooter = False
        .EvenPage.LeftHeader.Text = ""
        .EvenPage.CenterHeader.Text = ""
        .EvenPage.RightHeader.Text = ""
        .EvenPage.LeftFooter.Text = ""
        .EvenPage.CenterFooter.Text = ""
        .EvenPage.RightFooter.Text = ""
        .FirstPage.LeftHeader.Text = ""
        .FirstPage.CenterHeader.Text = ""
        .FirstPage.RightHeader.Text = ""
        .FirstPage.LeftFooter.Text = ""
        .FirstPage.CenterFooter.Text = ""
        .FirstPage.RightFooter.Text = ""
    End With

       'Mise en page avant impretion CAMP

     Sheets("STAGIAIRE CAMP").Select

      With ActiveSheet.PageSetup
        .PrintTitleRows = ""
        .PrintTitleColumns = ""
    End With
    ActiveSheet.PageSetup.PrintArea = ""
    With ActiveSheet.PageSetup
        .LeftHeader = ""
        .CenterHeader = ""
        .RightHeader = ""
        .LeftFooter = ""
        .CenterFooter = ""
        .RightFooter = ""
        .LeftMargin = Application.InchesToPoints(0.787401575)
        .RightMargin = Application.InchesToPoints(0.787401575)
        .TopMargin = Application.InchesToPoints(0.984251969)
        .BottomMargin = Application.InchesToPoints(0.984251969)
        .HeaderMargin = Application.InchesToPoints(0.5)
        .FooterMargin = Application.InchesToPoints(0.5)
        .PrintHeadings = False
        .PrintGridlines = False
        .PrintComments = xlPrintNoComments
        .CenterHorizontally = False
        .CenterVertically = False
        .Orientation = xlLandscape
        .Draft = False
        .PaperSize = xlPaperLetter
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = 100
        .PrintErrors = xlPrintErrorsDisplayed
        .OddAndEvenPagesHeaderFooter = False
        .DifferentFirstPageHeaderFooter = False
        .ScaleWithDocHeaderFooter = True
        .AlignMarginsHeaderFooter = False
        .EvenPage.LeftHeader.Text = ""
        .EvenPage.CenterHeader.Text = ""
        .EvenPage.RightHeader.Text = ""
        .EvenPage.LeftFooter.Text = ""
        .EvenPage.CenterFooter.Text = ""
        .EvenPage.RightFooter.Text = ""
        .FirstPage.LeftHeader.Text = ""
        .FirstPage.CenterHeader.Text = ""
        .FirstPage.RightHeader.Text = ""
        .FirstPage.LeftFooter.Text = ""
        .FirstPage.CenterFooter.Text = ""
        .FirstPage.RightFooter.Text = ""
    End With

    'Réorganisé les feuilles

    Sheets("STAGIAIRE CAMP").Select
    Sheets("STAGIAIRE CAMP").Move Before:=Sheets(2)

    Sheets("STATISTIQUE").Select
    Sheets("STATISTIQUE").Move Before:=Sheets(3)

End Sub

22liste-de-stagiaire-test.xlsx (14.82 Ko)

Le 31/08/2017 à 13:32

Re,

Expliquez-nous ce que tu veux extraire dans l'onglet "Extraction". Une ou deux lignes suffit pour voir ce que tu attends. On te proposera un code.

Le 31/08/2017 à 20:01

Re,

je veux sélectionner toute les cellules de mon tableau. Par défaut j'ai mis ("A1:I200") mais si demain j'ai 400 stagiaires, mon algorithme ne marche plus.

 Set plage = Range("A1:I200")

    For Each cel In plage
     cel.Interior.ColorIndex = xlColorIndexNone

    Next cel

Après je souhaite compter mon nombre de stagiaires. il me faut savoirs le nombre de stagiaires total, le nombre de stagiaires de type CAMP et le nombre de stagiaire qui ne sont pas de Type CAMP (ma variable CMN). Le problème c'est étant donné que ma plage fait 200 ligne mon algorithme me comte également les cellules vides ( je pourrais utiliser un moyen détourné mais j'ai plusieurs problèmes de la sort et il me serait plus confortable de pouvoir sélectionner uniquement les cellules qui m’intéresse).

Set plage = Range("E2:E200")

For Each cel In plage

    If cel.Value Like "*CAMP*" Then
   CAMP = CAMP + 1
   Else
   CMN = CMN + 1

    End If
    Next cel

Idéalement j'aimerais avoir une variable plage qui contient toutes les cellules de mon tableau. puis une autre variable plageE qui contient toute les cellules de la colonne E etc.

Ps: je crois qui y a eu un quiproquo. Chaque semaines je télécharge un fichier tel que celui que j'ai fourni. Mais le nombre de stagiaires est toujours différent.

Raja a écrit :
Expliquez-nous ce que tu veux extraire dans l'onglet "Extraction"

Je veux rien faire dans l'onglet extraction ???

En tout cas merci raja.

Le 01/09/2017 à 09:52

Re,

Essaye ça et adapte ton code si c'est bien cela que tu veux :

Option Explicit
Sub test()
    Dim plage As Range, nbCamp As Integer, nbTotal As Integer
    With Sheets("Stagiaire")
        'plage dynamique
        Set plage = .Range("E2:E" & .Range("E" & Rows.Count).End(xlUp).Row)
        'nombre total de stagiaires sans les cellules vides
        nbTotal = WorksheetFunction.CountA(plage)
        'nombre total de stagiaires CAMP
        nbCamp = WorksheetFunction.CountIf(plage, "*" & "CAMP" & "*")
        MsgBox "Nombre de stagiaires CAMP : " & nbCamp
        MsgBox "Nombre de stagiaires autre que CAMP : " & nbTotal - nbCamp
        Set plage = Nothing
    End With
End Sub

Le 01/09/2017 à 11:06

Bonjour,

2 exemples avec TCD :

avec l'ajout d'une colonne et un TCD.
TCD2 sans ajout de colonne mais en créant 2 groupes manuellement

Tableau Croisé Dynamique (TCD) : http://www.mdf-xlpages.com/modules/smartsection/item.php?itemid=109

eric

21liste-de-stagiaire-test.xlsx (27.20 Ko)

Le 02/09/2017 à 10:01

Re,

merci de vos réponse

Set plage = .Range("E2:E" & .Range("E" & Rows.Count).End(xlUp).Row)

Ce code m'a bien aidé. Existe t'il la même chose pour un nombre indéterminé de colonnes? Et pour un nombre indéterminée de colonnes et de lignes?

j'ai essayé de remplacer Rows par Columns mais ça ne marche pas.

Set plage = .Range("A1:" & .Range("A" & Rows.Count).End(xlUp).Row & .Range("A" & Columns.Count).End(xlLeft).Columns)

Les TCD ne sont pas ce qu'il y a de plus adapter pour ce que je veux faire.

Le 02/09/2017 à 11:05

Bonjour,

Pour le principe.

A adapter à ton cas précis.

Cdlt.

Public Sub DEMO()
Dim cn As Long, rw As Long
Dim rng As Range
    With ActiveSheet
        cn = .Cells(1, .Columns.Count).End(xlToLeft).Column
        rw = .Cells(.Rows.Count, 1).End(xlUp).Row
        Set rng = .Cells(1).Resize(rw, cn)
    End With
End Sub

Public Sub DEMO2()
Dim rng As Range
    With ActiveSheet
        Set rng = .Cells(1).CurrentRegion
    End With
End Su

Le 02/09/2017 à 11:50

Bonjour,

Les TCD ne sont pas ce qu'il y a de plus adapter pour ce que je veux faire.

Ah bon ? C'est pourtant la raison d'être de leur existence.

Ca te donne ton nombre en 2 lignes de code dont une pour le rafraichir.

Mais bon, si ça va pas...

eric

Le 08/09/2017 à 09:45

Merci,

J'ai fini par trouver grâce a votre aide.

Rechercher des sujets similaires à "selectioner tableau taille variable"