Number Of Rows with Data

billandrew

Well-known Member
Joined
Mar 9, 2014
Messages
743
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
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Fluff

This works perfect as well.

Modified as well to exclude the top row

MsgBox "Number of Rows with data in Column b is" & " " & [counta(B:B)] - 1
 
Upvote 0
Interested in understanding why the original code returned an incorrect value.
It not an incorrect value End(xlup) is the same as pressing Ctrl + the up arrow which takes you to the top cell in the column if it is empty so B1 or to the first cell above the start cell with data if not an empty column (strictly speaking it takes you to the edge of the data range).

BUT...

Mark

This results in 87800 two times

Then it is not empty

A) See Fluffs post number 14

B) If not A) what does the below return?
Code:
Sub test2()
MsgBox Cells(Rows.Count, 2).End(xlUp).Formula
MsgBox Split(Cells(Rows.Count, 2).End(xlUp).Address(0, 0, 0, 1, 0), "]")(1)
End Sub
 
Upvote 0
Not entirely sure what you mean.

The most recent code returns a blank Message Box and the subsequent message box returns RC[1

Thank You
 
Upvote 0
Your workbook obviously hasn't been saved which is why you get RC1[ (BTW that isn't what it would have returned it would have returned the sheet name in front of it with an exclamation mark, it is important when we ask what something returns that you are exact).

I take it then that you did have a header with 87800?
 
Upvote 0
You are correct the sheet name was in front. I didn't realize that was essential.

No sir there was no header.

All I'm trying accomplish is to learn something...
 
Upvote 0
Not making much sense, can you save the workbook to your desktop and then let me know what the 2nd message box returns, it should return your correct sheet name and $B$1.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,207
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top