Excel VBA - Cells Counting using VBA codes and displaying results in msg box

[wuming]

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 ar
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 fro
displaying in excel to using vba so that the user interface using m
program would look "nicer". I have created userforms that look lik
menus for the program, but i am unable to use the above coding as the
are not in vba codes. What i need now is them to be in vba codes and t
display the results in msg boxes thus enabling the program to look mor
professional. As i am totally new to excel vba, i can only hope tha
those that are more proficient in it can help out by posting th
solutions. Thanks in advanceD

 

[Norman Jones]

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

 

[JulieD]

Hi

don't know if this is the best way, but i used this code recently to return
the number of rows & columns ...and display the results in a message box

Range("A1").Select
MsgBox "the number of rows is " &
ActiveCell.SpecialCells(xlLastCell).Row
MsgBox "the number of columns is " &
ActiveCell.SpecialCells(xlLastCell).Column

Cheers
JulieD

 

[Bob Phillips]

Msgbox WorksheetFunction(COUNTA(Range("Table!A:A")))
Msgbox WorksheetFunction(COUNTA(Range("Table!1:1")))
Msgbox WorksheetFunction(COUNT(Range("Table!B2:Z60000")))
Msgbox WorksheetFunction(COUNTIF(Range("Table!B2:Z60000"),"*"))
Msgbox CountAlphas(Range("Table!B2:Z60000)")

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Norman Jones]

I think Bob intended:

MsgBox WorksheetFunction.CountA(Range("Table!A:A"))
MsgBox WorksheetFunction.CountA(Range("Table!1:1"))
MsgBox WorksheetFunction.Count(Range("Table!B2:Z60000"))
MsgBox WorksheetFunction.CountIf(Range("Table!B2:Z60000"), "*")
MsgBox CountAlphas(Range("Table!B2:Z60000"))

 

[Bob Phillips]

I knew it looked wrong, but wood for the trees<g>.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[wuming]

ah many thanks to all!