Always ON Row Count

oskar

New member
Joined
May 10, 2013
Messages
24
Reaction score
0
Points
0
Location
Montreal, Canada
Excel Version(s)
Office 2013
Following a VBA lesson from the web I made a contacts DB and now I have in sheet1 my ActiveX userform and in sheet2 I have my dynamic range DB (a typical address layout with 151 rows).

I would like to but a box in the userform (cmd button, or txtbox or what is appropriate) to indicate the number of used rows in sheet2. In other words this box will tell me the total number of contacts since each row is one contact

I don’t want to press any button to get the number. The number will always be on and change as I add / delete contacts.

This is a hobby project and I have a basic knowledge of VBA (Excel 2013, student)

Thank you
 
If the first row is headings (it should be), you will need to subtract 1 from the count as Roger gave it.
 
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Column = 1 Then Application.StatusBar = Columns(1).SpecialCells(2).Count-1
End Sub
 
I made a Label and a control button and it works nicely when I click the control button.

Is there a way to have the Label indicating the total number of rows without clicking a control?
 
When I put the code in post #2 in a blank workbook and fill out some rows it works but when I put the same code in my contacts db (after I change the sheet ref) I get: error 9, subscript out of range.

I can’t figure this out, any ideas what I do wrong?
 
This worked for me

Code:
Private Sub commandButton21_click()
 Dim Count As Integer
    Range("b9").Select
        Range(Selection, Selection.End(xlDown)).Select
Sheet1.Label218.Caption = "" & Selection.Cells.Count
    Range("b9").Select
End Sub
 
Back
Top