Number Of Rows with Data

[billandrew]

Afternoon

Using the below to return the Number of Rows in Column B. The Column currently does not have any data, when the code is run the result is 1. Not sure why this is.

Sub CountRowsColumn()

MsgBox "Number of Rows with data in Column b is" & " " & Cells(Rows.Count, 2).End(xlUp).Row

 

[Fluff]

If you have no data in the column then this

Cells(Rows.Count, 2).End(xlUp)

will take you to the top row in the sheet, ie row 1

 

[billandrew]

I removed the .Row

Result is "Number of rows with Data in Column B is 87800"

 

[Fluff]

That suggests that B1 contains 87800

 

[Rick Rothstein]

Assuming there are no formulas in Column B, this code snippet will report the number of cells in Column B that contain data (empty cells will not be counted)...

[table="width: 500"]
[tr]
	[td]  Dim FilledCellCount As Long
  On Error Resume Next
  FilledCellCount = Columns("B").SpecialCells(xlConstants).Count
  On Error GoTo 0
  MsgBox "Number of Rows with data in Column B is " & FilledCellCount & "."[/td]
[/tr]
[/table]

 

[billandrew]

Fluff

The column contains no data.

 

[MARK858]

Bill, what result does the below give?

Sub test()
MsgBox Cells(Rows.Count, 2).End(xlUp).Value
MsgBox Cells(Rows.Count, 2).End(xlUp).Value2
End Sub

 

[Fluff]

What does this return

Sub chk()
MsgBox "Number of Rows with data in Column b is" & " " & [counta(B:B)]
End Sub

Also what results do you get from Rick's & Mark's suggestions?

 

[billandrew]

This works Mr.Rothstein

 

[billandrew]

Modified it a bit to exclude the first row or Header

FilledCellCount = Columns("B").SpecialCells(xlConstants).Count - 1