Get number of rows with data in a Range in Excel

My excel sheet looks like this
row 1 to row 150 have data and formating
row 151 to row 1600 have formating only

I need to find out the last row with DATA ONLY in Excel, however when I use the code below the count shows 1600 - all rows that have cell formating (backcolor etc) applied even if those cells have no data in them.

VB Code:

UsedRange.Rows.Count

I also tried these with same problem:

VB Code:

ws.Cells.SpecialCells(xlCellTypeLastCell).row
  xlApp.ActiveCell.SpecialCells(xlLastCell).Address(RowAbsolute:=False)
  ws.UsedRange.Rows.Count
  ActiveCell.SpecialCells(xlLastCell).row

How can I get the count of all the rows with data only.

You could try using the const. xlCellTypeBlanks. Cells with formatting may or may not be considered blank.
I cant remember, but the const. xlCellTypeSameFormatConditions will get you the cells with the same formats,
this may also help.

Thanks RobDog888 I'll try that.

Could you help me with this one as well? I'm stuck.

http://www.vbforums.com/showthread.p...ht=Excel+array

You can also do this:

VB Code:

Dim topCel As Range, bottomCel As Range
Dim NoOfRows As Integer, a As Integer
 
'Count entries
With ActiveSheet
    Set topCel = .Range("A1").
    Set bottomCel = .Cells((65536), topCel.Column).End(xlUp)
        If bottomCel.Row <= topCel.Row Then
        Exit Sub
        End If
NoOfRows = .Range(topCel, bottomCel).Rows.Count
End With