Get number of rows with data in a Range in Excel

[Tomexx]

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.

UsedRange.Rows.Count

I also tried these with same problem:

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.

[RobDog888]

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.

[Tomexx]

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

[D-niss]

You can also do this:

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