Row count Excel

Rayne
Rayne used Ask the Experts™
on
Hello,


Sub countRows()
ActiveSheet.Range("C3").Select
Dim s As Long
'MsgBox ("Rows to end: " & Selection.End(xlDown).Row - (Selection.Row - 1))
s = Selection.End(xlDown).Row - (Selection.Row - 1)
MsgBox "count: " & s
TextBox1.Text = s
End Sub

I am not sure what I am missing….
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Author

Commented:
I am trying to count the number of rows in a worksheet and then feed that in a textbox for display to users

Author

Commented:
.this is giving errors..
This perhaps?
Sub countRows()
    
Dim s As Long

s = Range("C3").End(xlDown).Row - 2
MsgBox "count: " & s
UserForm1.TextBox1.Text = s

End Sub

Open in new window

I've done it this  way
------------------------------------
....

b = Range("C3", Range("C" & Rows.Count).End(xlUp)).Value
s = UBound(b, 1)

MsgBox "count: " & s
UserForm1.TextBox1.Text = s

......
However, thinking about it, you would probably need to put this code in the userform module.

Author

Commented:
I am still getting a object required error

Author

Commented:
try this

ActiveSheet.TextBox1.Value = s

Author

Commented:
Thank you Steve - Awesome that works but there is one issue – can you get it to refresh as I delete or add more row. For example if user adds new rows or delete row, the text box value display refreshes automatically?? Is that achievable? So in essence no need for the test button…

Author

Commented:
I increased the points because I really need to make this module work nice :)
Added an event handler Worksheets_Change on sheet 1
count.xlsm

Author

Commented:
Perfect as it can get. Thank you Steve, that is a life saver :)

Author

Commented:
Perfect

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial