Hi Wuming,


Sub Tester()

'// (1)
MsgBox "# Rows in Table =" _
& Activesheet.Range("Table").Rows.Count

'// (2)
MsgBox "# Columns in Table = " _
& Activesheet.Range("Table").Columns.Count

'// (3)
MsgBox "# Numeric cells in Table = " _
& Application.Count(Activesheet.Range("Table"))

'// (4)
MsgBox "# AlphaNumeric cells in Table = " _
& Application.CountA(Activesheet.Range("Table"))

'// (5)
MsgBox "#Alpha cells in Table = " _
& CountAlphas(Activesheet.Range("Table"))

End Sub


---
Regards,
Norman



"wuming " wrote in message
...
Hi All

Previously i have asked in the forum for help in counting:
1. No. of rows
2. No. of columns
3. No. of numeric cells
4. No. of alphanumeric cells
5. No. of alphabetic cells
6. No. of NULL cells
The below are what i have used using excel meaning the results are
shown in excel file format (displayed in cells):
1. COUNTA(Table!A:A))
2. COUNTA(Table!1:1))
3. COUNT(Table!B2:Z60000))
4. COUNTIF(Table!B2:Z60000,"*"))
5. CountAlphas(Table!B2:Z60000)) -- code below

Function CountAlphas(CountRange As Range) As Long
Dim cell As Range
Dim iCtr As Long

For Each cell In CountRange
If Not IsEmpty(cell) Then
If Not cell.Value Like "*[0-9]*" Then
iCtr = iCtr + 1
End If
End If
Next
CountAlphas = iCtr
End Function

6. COUNTBLANK(Table!B2:Z60000)

However the biggest problem now is that i have to switch from
displaying in excel to using vba so that the user interface using my
program would look "nicer". I have created userforms that look like
menus for the program, but i am unable to use the above coding as they
are not in vba codes. What i need now is them to be in vba codes and to
display the results in msg boxes thus enabling the program to look more
professional. As i am totally new to excel vba, i can only hope that
those that are more proficient in it can help out by posting the
solutions. Thanks in advanceD!


---
Message posted from http://www.ExcelForum.com/