Hi guys

Please see the code below. I want to display the total number of visible records after autofilter and total amount at the bottom of the printed sheet. If I don't use j=j+1 then at first attempt it overlaps the last used row in excelsheet with the total and if I use it then everytime we press the button the total will appear 1 row down. But I always want to display the total after suppose 2 rows of the filtered data so that it will show us exactly this is the data and after that this is the total.

Private Sub CommandButton1_Click()
j = Worksheets("sheet1").UsedRange.Rows.Count
j = j + 1
If OptionButton1.Value = True Then
     Range("A1").Select
    Selection.AutoFilter
    Selection.AutoFilter Field:=1, Criteria1:=DateSerial(Y, M, D)
    Answer = MsgBox("Do you want to print this report?", vbQuestion + vbYesNo + vbDefaultButton2)
    If Answer = vbYes Then
    Set filteredRange = ActiveSheet.AutoFilter.Range
  
i = filteredRange.Columns(1).SpecialCells(xlCellTypeVisible).Count - 1

Set Rng = Worksheets("sheet1").Range("A2:F2")
    Set RngEnd = Worksheets("sheet1").Cells(Rows.Count, Rng.Column).End(xlUp)
    If RngEnd.Row < Rng.Row Then Exit Sub Else Set Rng = Worksheets("sheet1").Range(Rng, RngEnd)
       
      For Each Cell In Rng.Columns("D").Cells
        If Cell.Rows.Hidden = False Then
               If Cell.Columns.Hidden = False Then
                   total = total + Cell.Value
               End If
           End If
       Next
With Worksheets("sheet1")
    .Cells(j, 1).Font.Bold = True
    .Cells(j, 1).Value = "Total Cheques=" & i
    .Cells(j, 3).Font.Bold = True
    .Cells(j, 3).Value = "Total Amount=" & total
End With
    Worksheets("sheet1").PrintOut
     MsgBox "The report has been printed"
     With Worksheets("sheet1")
    .Cells(j, 1).Font.Bold = False
    .Cells(j, 1).Value = " "
    .Cells(j, 3).Font.Bold = False
    .Cells(j, 3).Value = " "
    .Cells(2, 1).Value = "14/09/2010"
End With
    End If
I hope you understand my problem.

Thanks