Solved: count rows excluding header row

[lhtqasonline]

Hi

I need assistance with code copied from another thread.
I want to be able to count the amount of rows in a sheet.
While the below code works i need it to exclude the header row.

MsgBox Intersect(Range("A:A"),Cells.SpecialCells(xlCellTypeConstants).EntireRow).C ount

Also need the value in a cell on the spreadsheet and not on a msgbox

Any assistance appreciated

[Bob Phillips]

Subtract 1?

[lhtqasonline]

Sorry i'm a newbie to VBA
How do i do that

[Bob Phillips]

[vba]

MsgBox Intersect(Range("A:A"),Cells.SpecialCells(xlCellTypeConstants).EntireRow).C ount - 1
[/vba]

[lhtqasonline]

Sorry won't forget this one, i thought it would involve complex argument to subtract 1

Thanks xld

[lhtqasonline]

How can i use the same formula but insert the value in cell and not on a msgbox

[Zack Barresse]

Where would you want it? ...
[vba]With ActiveSheet
.Range("A2").Value = Intersect(Range("A:A"),Cells.SpecialCells(xlCellTypeConstants).EntireRow).C ount - 1
End With[/vba]

You haven't specified much, but that should do the basics.

HTH

[lhtqasonline]

Thank you that did the trick