Add number of used rows to footer?

  • Thread starter Thread starter Elwood Dowd
  • Start date Start date
E

Elwood Dowd

Is it possible to add the number of used rows to a spreadsheet's
footer? I have a basic list in Excel 2000. the number of rows in the
list changes weekly, and I would like to find a simple way to:

1 - Count the number of rows in the current list (minus the header
row)

2 - Display this number in the footer of the printed spreadsheet.

If anyone can help with either of these, I'd appreciate it!

Thanks,
E
 
If there are no embedded blank cells in column A, you can get a count of the filled rows (minus
the header) with the formula =COUNTA(A:A) - 1

To put that number in a footer, you need to use the event macro, Workbook.Before_Print

Private Sub Workbook_BeforePrint(Cancel As Boolean)
With Worksheets("Sheet1")
.PageSetup.RightFooter = Application.CountA(.Columns(1)) - 1
End With
End Sub

That code goes in the module named ThisWorkbook. Change the sheet name and column to check, as
needed.
 
Back
Top