Count of rows in header

swjtx99
swjtx99 used Ask the Experts™
on
I am currently using the following to count and return the number of rows on the sheet that contain data, in the header. The problem is that the SpecialCells doesn't work after deleting some lines (unless I resave and that is not possible) so I am searching for an alternative.

    ActiveSheet.PageSetup.PrintArea = ""
    With ActiveSheet.PageSetup
        .LeftHeader = "&""Arial,Bold""&10Count = " & Cells.SpecialCells(xlCellTypeLastCell).Row - 1

Thanks in advance,

swjtx99
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Top Expert 2012
Commented:
This will give you the last row, independent of which column actually has the most rows of data:

Dim lastRow As Long

    lastRow = Cells.Find(what:="*", LookIn:=xlValues, lookat:=xlPart, SearchOrder:=xlByRows, searchdirection:=xlPrevious).Row

Open in new window


So try:

Dim lastRow As Long

    ActiveSheet.PageSetup.PrintArea = ""

    lastRow = Cells.Find(what:="*", LookIn:=xlValues, lookat:=xlPart, SearchOrder:=xlByRows, searchdirection:=xlPrevious).Row
    With ActiveSheet.PageSetup
        .LeftHeader = "&""Arial,Bold""&10Count = " & lastRow - 1

Open in new window



Dave

Author

Commented:
Hi Dave,

Thank you very much. Works perfectly.

swjtx99

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial