PDA

View Full Version : Solved: count rows excluding header row



lhtqasonline
01-19-2009, 07:11 AM
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).Count

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

Any assistance appreciated

Bob Phillips
01-19-2009, 07:13 AM
Subtract 1?

lhtqasonline
01-19-2009, 07:19 AM
Sorry i'm a newbie to VBA
How do i do that

Bob Phillips
01-19-2009, 07:33 AM
MsgBox Intersect(Range("A:A"),Cells.SpecialCells(xlCellTypeConstants).EntireRow).Count - 1

lhtqasonline
01-19-2009, 07:37 AM
:rotlaugh:
Sorry won't forget this one, i thought it would involve complex argument to subtract 1

Thanks xld

lhtqasonline
01-19-2009, 08:02 AM
How can i use the same formula but insert the value in cell and not on a msgbox:dunno

Zack Barresse
01-19-2009, 08:10 AM
Where would you want it? ...
With ActiveSheet
.Range("A2").Value = Intersect(Range("A:A"),Cells.SpecialCells(xlCellTypeConstants).EntireRow).Count - 1
End With

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

HTH

lhtqasonline
01-19-2009, 08:19 AM
Thank you that did the trick
:hifive: