Attribute VB_Name = "AClassHF"
Public Vfl, VflA, VflB, VflC, Vcolf, VcolfA As Long
Public RgImp As Range
Public VNbCel As Integer
Public Vadd As String

' Création onglet résultats garçons filles

Sub EditM()

Sheets("ResF").Select

    Columns("A:S").Delete
    Columns("A:S").Insert

Sheets("ResM").Select

    Columns("A:S").Delete
    Columns("A:S").Insert

' recopier tab resultat affich sur ResM
Sheets("Resultats (affichage)").Select
    Range("A:S").Copy
    Sheets("ResM").Select
    Columns("A:S").PasteSpecial xlPasteValues
    Columns("A:S").PasteSpecial xlPasteFormats
    
 'supprimer colonnes inutiles
    Columns("G:I").Delete
    Columns("J:M").Delete
    
    
  'Taille document
  Vfl = Range("A1").End(xlDown).Row
  
  Range("M2").FormulaR1C1 = "=if(RC[-12]="""",2,1)"
  Range("M2").Copy Destination:=Range("M3:M" & Vfl)
  Range("M2:M" & Vfl) = Range("M2:M" & Vfl).Value
  
  Set rgfound = Range("M2:M" & Vfl).Find(2)
  
   VflA = rgfound.Row
     
  Vcolf = Range("A1").End(xlToRight).Column
  
  'Supprimer lignes vides
  Rows(VflA & ":" & Vfl).Select
  Selection.Delete

Columns("M:M").Delete


If Not Sheets("ResM").AutoFilterMode Then Sheets("ResM").[A10].AutoFilter

    [A1].AutoFilter Field:=10, Criteria1:="F", Operator:=xlAnd
    
       n = [Subtotal(3, A:A)] - 1
       
       If n < 1 Then
            MsgBox "Pas d'élèves de sexe féminin"
        Else
        
        Range("A1").CurrentRegion.Copy Destination:=Sheets("ResF").Range("A1")
        
       End If
    
    Sheets("ResM").Select
    Range([A2], [A2].End(xlDown)).Select
    Selection.SpecialCells(xlCellTypeVisible).Select
    Selection.EntireRow.Delete
    
    ActiveSheet.ShowAllData
    
 ' Refaire classement

    Sheets("ResM").Select
        VflB = Range("A1").End(xlDown).Row
    
        Range("A2").FormulaR1C1 = "1"
        Range("A3").FormulaR1C1 = "=R[-1]C+1"
        Range("A3").Copy Destination:=Range("A4:A" & VflB)
        Range("A2:A" & VflB) = Range("A2:A" & VflB).Value
        
        Rows(VflB + 1 & ":" & 5000).Select
        Selection.Delete
       
     Sheets("ResF").Select
        VflB = Range("A1").End(xlDown).Row
       
       Range("A2").FormulaR1C1 = "1"
        Range("A3").FormulaR1C1 = "=R[-1]C+1"
       Range("A3").Copy Destination:=Range("A4:A" & VflB)
        Range("A2:A" & VflB) = Range("A2:A" & VflB).Value
        
        'Ajuster taille colonne
         Sheets("ResF").Select
         Columns("A:L").AutoFit

       Range("L1:L" & VflB).Copy Destination:=Range("M1")
       Range("M2:M" & VflB).FormulaR1C1 = "=LEFT(RC[-8],FIND("" "",RC[-8])-1)"
       Range("M2:M" & VflB).Formula = Range("M2:M" & VflB).Value
       Range("M1").Formula = "Cat Classe"
       Range("M:M").ColumnWidth = "7.80"
       
       Range("O2").FormulaR1C1 = "=COUNTIF(RC[-9]:R[" & VflB & "]C[-9],"">""&0)"
       
       If (Range("O2") = 0) Then Columns("F:F").EntireColumn.Hidden = True
        
       
       Range("O2").FormulaR1C1 = "=COUNTIF(RC[-6]:R[" & VflB & "]C[-6],"">""&0)"
       
       If (Range("O2") = 0) Then Columns("I:I").EntireColumn.Hidden = True
       
       Range("O2").Clear
       
        
      Sheets("ResM").Select
        VflB = Range("A1").End(xlDown).Row
        
        
      

    Sheets("ResM").Select
     'Ajuster taille colonne

    [A1].AutoFilter
    Columns("A:L").AutoFit
   
    
     Range("L1:L" & VflB).Copy Destination:=Range("M1")
     Range("M2:M" & VflB).FormulaR1C1 = "=LEFT(RC[-8],FIND("" "",RC[-8])-1)"
     Range("M2:M" & VflB).Formula = Range("M2:M" & VflB).Value
     Range("M1").Formula = "Cat Classe"
     Range("M:M").ColumnWidth = "7.80"
    
    Range("O2").FormulaR1C1 = "=COUNTIF(RC[-9]:R[" & VflB & "]C[-9],"">""&0)"
    
    If (Range("O2") = 0) Then Columns("F:F").EntireColumn.Hidden = True
     
    
    Range("O2").FormulaR1C1 = "=COUNTIF(RC[-6]:R[" & VflB & "]C[-6],"">""&0)"
    
    If (Range("O2") = 0) Then Columns("I:I").EntireColumn.Hidden = True
    
    Range("O2").Clear
        
        
  

End Sub

' Impression Résultats Garçons

Sub ImpM()

Sheets("ResM").Select

VflA = Range("A1").End(xlDown).Row

Set RgImp = Range("A1:M" & VflA)

'Range("RgImp").Select
    ActiveSheet.PageSetup.PrintArea = "$A$1:$M$" & VflA
    Application.CutCopyMode = False
    'Application.PrintCommunication = False
    
    With ActiveSheet.PageSetup
        .PrintTitleRows = "$1:$1"
    End With
   'Application.PrintCommunication = True
   ActiveSheet.PageSetup.PrintArea = "$A$1:$M$" & VflA
    'Application.PrintCommunication = False
    
    With ActiveSheet.PageSetup
        .LeftHeader = ""
        .CenterHeader = ""
        .RightHeader = ""
        .LeftFooter = ""
        .CenterFooter = ""
        .RightFooter = ""
        .LeftMargin = Application.InchesToPoints(0)
        .RightMargin = Application.InchesToPoints(0)
        .TopMargin = Application.InchesToPoints(0)
        .BottomMargin = Application.InchesToPoints(0)
        .HeaderMargin = Application.InchesToPoints(0)
        .FooterMargin = Application.InchesToPoints(0)
        
    End With
    'Application.PrintCommunication = True
    Sheets("ResM").PrintOut
End Sub

' Impression Résultats Filles


Sub ImpF()

Sheets("ResF").Select

VflA = Range("A1").End(xlDown).Row

Set RgImp = Range("A1:M" & VflA)

    ActiveSheet.PageSetup.PrintArea = "$A$1:$M$" & VflA
    Application.CutCopyMode = False
    'Application.PrintCommunication = False
    
    With ActiveSheet.PageSetup
        .PrintTitleRows = "$1:$1"
    End With
    
    'Application.PrintCommunication = True
    ActiveSheet.PageSetup.PrintArea = "$A$1:$M$" & VflA
    'Application.PrintCommunication = False
    
    With ActiveSheet.PageSetup
        .LeftHeader = ""
        .CenterHeader = ""
        .RightHeader = ""
        .LeftFooter = ""
        .CenterFooter = ""
        .RightFooter = ""
        .LeftMargin = Application.InchesToPoints(0)
        .RightMargin = Application.InchesToPoints(0)
        .TopMargin = Application.InchesToPoints(2)
        .BottomMargin = Application.InchesToPoints(0)
        .HeaderMargin = Application.InchesToPoints(0)
        .FooterMargin = Application.InchesToPoints(0)
        
    End With
   ' Application.PrintCommunication = True
    
    Sheets("ResF").PrintOut
End Sub

'Mise page onglet Bilan par classe
Sub ImpClas()

Sheets("Bilan par classe").Select


For T = 2 To 84 Step 7

    If Cells(3, T) = "" Then
    
        Vadd = Cells(59, T - 2).Address
        Vvcolf = Cells(3, T - 1).Column
        Exit For
    
    Else
    
    End If

Next T

'zone imp

Set RgImp = Range("$A$1:" & Vadd)

ActiveSheet.PageSetup.PrintArea = "$A$1:" & Vadd
    Application.CutCopyMode = False
    'Application.PrintCommunication = False
    
    With ActiveSheet.PageSetup
        .PrintTitleRows = "$1:$1"
    End With
    
    'Application.PrintCommunication = True
    ActiveSheet.PageSetup.PrintArea = "$A$1:" & Vadd
    'Application.PrintCommunication = False
    
    With ActiveSheet.PageSetup
        .LeftHeader = ""
        .CenterHeader = ""
        .RightHeader = ""
        .LeftFooter = ""
        .CenterFooter = ""
        .RightFooter = ""
        .LeftMargin = Application.InchesToPoints(0)
        .RightMargin = Application.InchesToPoints(0)
        .TopMargin = Application.InchesToPoints(0)
        .BottomMargin = Application.InchesToPoints(0)
        .HeaderMargin = Application.InchesToPoints(0)
        .FooterMargin = Application.InchesToPoints(0)
        
    End With
   ' Application.PrintCommunication = True
 
  

End Sub

' Mise a jour Classement par classe

Sub ClasParClasse()

Sheets("Bilan par classe").Select

For i = 2 To 80 Step 7

    If Cells(3, i).Value <> "" And Cells(31, i).Value <> "" Then

        For T = 3 To 30
        
            Cells(T, i).Select
            
            vcel = ActiveCell.Value
            
            If vcel <> "" Then
                
            Else
            VflC = ActiveCell.Row - 1
                Exit For
            End If
        
        Next T
        
        Cells(3, i).Formula = 1
        Range(Cells(3, i), Cells(VflC, i)).Select
        Selection.DataSeries Rowcol:=xlColumns, Type:=xlLinear, step:=1, Stop:=VflC, Trend:=False
        
        Else
    
    End If

Next i

'Garçons

For i = 2 To 80 Step 7

    If Cells(31, i).Value <> "" Then

        For T = 31 To 58
        
            Cells(T, i).Select
            
            vcel = ActiveCell.Value
            
            If vcel <> "" Then
                
            Else
            VflC = ActiveCell.Row - 1
                Exit For
            End If
        
        Next T
        
        Cells(31, i).Formula = 1
        Range(Cells(31, i), Cells(VflC, i)).Select
        Selection.DataSeries Rowcol:=xlColumns, Type:=xlLinear, step:=1, Stop:=VflC, Trend:=False
        
    Else
    
    Exit For
    
    End If

Next i


End Sub


Sub MasqLig()


Sheets("Bilan par classe").Select

' taille zone
    VflA = Range("A3").End(xlDown).Row
    Vcolf = Range("ZZ2").End(xlToLeft).Column
    VNbCel = Vcolf - (Vcolf / 7)
    
    VcolfA = Vcolf + 1
    
' pose formule detecter lignes vides
    Cells(3, VcolfA).FormulaR1C1 = "=IF(COUNTIF(RC[-84]:RC[-1],"""")>=" & VNbCel & ",2,1)"
    Cells(3, VcolfA).Copy Destination:=Range(Cells(3, VcolfA), Cells(VflA, VcolfA))
    Range(Cells(3, VcolfA), Cells(VflA, VcolfA)) = Range(Cells(3, VcolfA), Cells(VflA, VcolfA)).Value

'boucle pour masquer lignes vides

    For i = 3 To VflA
    
       Cells(i, VcolfA).Select
       If Cells(i, VcolfA) = 2 And Cells(i + 1, VcolfA) = 2 Then
       
            Rows(i & ":" & i).EntireRow.Hidden = True
        
        Else
        
        End If
        
    Next i
   

    Columns("CH").Copy
    Columns("CG").PasteSpecial xlFormats
    Columns("CG").ClearContents
    Application.CutCopyMode = False
    

Range("A3").Select
    
 End Sub

'Masquer Colonnes de Bilan

Sub MasqCol()

' Traiter colonnes vides
Sheets("Bilan par classe").Select
Application.CutCopyMode = False
VflA = Range("A3").End(xlDown).Row
    Vcolf = Range("ZZ2").End(xlToLeft).Column

    For i = 2 To Vcolf Step 7
        Cells(i, 2).Select
        
        If Cells(3, i).Value = "" And Cells(31, i) = "" Then
         
            Range(Cells(2, i - 1), Cells(2, 84)).EntireColumn.Hidden = True
            Exit Sub
         
        Else
         
        End If
    
    Next i

Range("A3").Select
End Sub

'Afficher Colonnes de Bilan
Sub Affcol()

    Sheets("Bilan par classe").Select
   Range("A1:CF1").EntireColumn.Hidden = False
   Range("A3").Select

End Sub
'Afficher lignes de Bilan
Sub AffLig()
    
    Sheets("Bilan par classe").Select
        Vfl = Range("A3").End(xlDown).Row
        Range("A1:A" & Vfl).EntireRow.Hidden = False
    Range("A3").Select
    
End Sub
' Afficher colonnes VMA
Sub DeMasqVma()
    ActiveSheet.Range("F:F").EntireColumn.Hidden = False
    ActiveSheet.Range("I:I").EntireColumn.Hidden = False
End Sub

' Masquer colonnes VMA
Sub MasqVma()
    ActiveSheet.Range("F:F").EntireColumn.Hidden = True
    ActiveSheet.Range("I:I").EntireColumn.Hidden = True
End Sub