Counting Keystrokes in Excel

J

jlane

We are having some data entry done, does anybody know a way to determin
the number of keystrokes in a worksheet range?

Sure would appreciate the help.

Thank
 
N

Norman Jones

Hi JLane,

Excel, unlike Word, does not have an inbuilt character count feature.

If the data entry comprises alphanumeric text without formulae, you could
use an array formula:

=SUM(LEN(A7:F100))

Array formulae are confirmed with Ctrl-Alt-Enter rather than Enter. Change
the range to suit.

To allow for formula entries, or as an alternative approach, you could use
VBA to write a user defined function.

For example, try:

Function CountKeystrokes(rng As Range) As Long
Dim rCell As Range
Dim iCtr As Long

For Each rCell In rng
iCtr = iCtr + Len(rCell.Formula)
Next
CountKeystrokes = iCtr
End Function

This function can be used as a worksheet function, e.g.

=CountKeystrokes(A1:F100")

or called from a sub:

Sub Demo()
'Specify a range (change to suit):
MsgBox CountKeystrokes(Sheets("Sheet1").Range("A1:F100"))

'or specify the entire sheet:
MsgBox CountKeystrokes(ActiveSheet.UsedRange)

End Sub

If you are unfamiliar with macros, see David McRitchie's Getting Started
with Macros and User Defined Functions notes at:

http://www.mvps.org/dmcritchie/excel/getstarted.htm
 
Top