Sub mise_en_page_sans_tronquer()
Dim tableau() As Variant
nombre_ligne_max_par_feuille = 25
nombre_ligne_totale = Range("A65000").End(xlUp).Row
'RAZ tous les sauts de page
ActiveSheet.ResetAllPageBreaks
With ActiveSheet.PageSetup
.FitToPagesWide = 1
.FitToPagesTall = 1
End With
'cacher/compter les lignes, ajouter les sauts de page
saut_page = 7 'saut initial
nb_saut = 1
n = 1
ReDim Preserve tableau(1 To 3, 1 To n)
'remplir un tableau contenant le nombre de ligne des tableaux
'cacher les lignes inutiles
For i = 7 To nombre_ligne_totale
If Cells(i, 1).Value = "" And Cells(i, 3).Value = "" Then
Rows(i).EntireRow.Hidden = True
Else
If Left(Cells(i, 3), 8) <> "Tableau " Then
tableau(1, n) = tableau(1, n) + 1
derniere_ligne = i
End If
End If
If Left(Cells(i, 3), 8) = "Tableau " Then
n = n + 1
ReDim Preserve tableau(1 To 3, 1 To n)
tableau(2, n - 1) = derniere_ligne
tableau(3, n) = i
End If
Next i
ActiveWindow.View = xlPageBreakPreview
print_area = "$A$6:"
For i = 1 To UBound(tableau, 2)
nb_ligne = nb_ligne + tableau(1, i)
If nb_ligne > nombre_ligne_max_par_feuille Then
If i = UBound(tableau, 2) Then
print_area = print_area & "$G$" & CStr(derniere_ligne)
Else
print_area = print_area & "$G$" & CStr(tableau(2, i)) & ",$A$" & CStr(tableau(3, i + 1)) & ":"
nb_ligne = 0
End If
Else
If i = UBound(tableau, 2) Then
print_area = print_area & "$G$" & CStr(derniere_ligne)
End If
End If
Next i
ActiveWindow.View = xlNormalView
ActiveSheet.PageSetup.PrintArea = print_area
End Sub
With ActiveSheet.PageSetup
[B].Zoom = False[/B]
.FitToPagesWide = 1
.FitToPagesTall = 1
End With
If Cells(i, 1).Value = "" And Cells(i, 3).Value = "" Then
Rows(i).EntireRow.Hidden = True
Else
Sub mise_en_page_sans_tronquer()
Dim tableau() As Variant
nombre_ligne_max_par_feuille = 70
nombre_ligne_totale = Range("A65000").End(xlUp).Row
'RAZ tous les sauts de page
ActiveSheet.ResetAllPageBreaks
With ActiveSheet.PageSetup
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
End With
'cacher/compter les lignes, ajouter les sauts de page
saut_page = 7 'saut initial
nb_saut = 1
n = 1
ReDim Preserve tableau(1 To 3, 1 To n)
'remplir un tableau contenant le nombre de ligne des tableaux
'cacher les lignes inutiles
For i = 7 To nombre_ligne_totale
If Cells(i, 1).Value = "" And Cells(i, 3).Value = "" Then
Rows(i).EntireRow.Hidden = True
Else
If Left(Cells(i, 3), 8) <> "Tableau " Then
tableau(1, n) = tableau(1, n) + 1
derniere_ligne = i
End If
End If
If Left(Cells(i, 3), 8) = "Tableau " Then
n = n + 1
ReDim Preserve tableau(1 To 3, 1 To n)
tableau(2, n - 1) = derniere_ligne
tableau(3, n) = i
End If
Next i
ActiveWindow.View = xlPageBreakPreview
print_area = "$A$6:"
For i = 1 To UBound(tableau, 2)
nb_ligne = nb_ligne + tableau(1, i) + 1 '(rajout d'1 ligne à chaque tableau)
If nb_ligne > nombre_ligne_max_par_feuille Then
If i > 1 Then i = i - 1
If i = UBound(tableau, 2) Then
print_area = print_area & "$G$" & CStr(derniere_ligne)
Else
print_area = print_area & "$G$" & CStr(tableau(2, i)) & ",$A$" & CStr(tableau(3, i + 1)) & ":"
nb_ligne = 0
End If
Else
If i = UBound(tableau, 2) Then
print_area = print_area & "$G$" & CStr(derniere_ligne)
End If
End If
Next i
ActiveWindow.View = xlNormalView
ActiveSheet.PageSetup.PrintArea = print_area
'Cells(1, 1) = print_area
End Sub