SousTotal Filtre Impression

[ummagumma]

Bonjour

Comment en vba
insérer un soustotal sur une plage filtrée sur place
puis définir comme zone d'impression
cette plage filtrée plus la ligne ou est inséré le soustotal

Mertci

 

[ummagumma]

Rebonjour


Apres quelques recherches
Sub test()
Dim rngAF As Range, i As Integer

With ActiveSheet
Set rngAF = .AutoFilter.Range.Offset(1, 0).Resize _
(.AutoFilter.Range.Rows.Count - 1).SpecialCells(xlCellTypeVisible)
'set a range = To visible cells (excluding the header)
End With
i = Application.WorksheetFunction.Subtotal(9, rngAF)
MsgBox i

End Sub

 

[JeFiltre]

Bonjour

Quelqu'un, svp pour rendre ce code plus concis?
L'objectif étant toujours :
de filtrer, sommer, imprimer (le tout par vba)

Sub DateFiltre()
Dim Rx2, Rx3, Rx4 As Range
Dim LRw As Long
If WorksheetFunction.CountA(Cells) > 0 Then
LRw = Cells.Find(What:="*", after:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
X = LRw + 1
End If

Set Rx2 = Range("A2", "A" & X - 1)
Set Rx3 = Range("B2", "B" & X - 1)
Set Rx4 = Range("C2", "C" & X - 1)
With ActiveSheet.Range("A2").CurrentRegion
.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("critere"), Unique:=False
End With

Cells(X, 1) = "=SUBTOTAL(9," & Rx2.Address & ")"
Cells(X, 2) = "=SUBTOTAL(9," & Rx3.Address & ")"
Cells(X, 3) = "=SUBTOTAL(9," & Rx4.Address & ")"

Set Rx2 = Nothing: Set Rx3 = Nothing: Set Rx4 = Nothing
End Sub