I have a column with many rows that may have blank cells in between them. how
can I get the instance or count of them?

For example in A column I have;

Q
S
Q
Q
H
H
C

Results should be;

3 Q
1 S
2 H
1 C

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200802/1

Since you're in the Programming group, here's a Tom Ogilvy macro.

Sub CountLetters()
''a count of each letter in a range outputted to a new sheet
Dim letCount(1 To 26) As Long
Dim wkSht As Worksheet
Dim ii As Long
Dim cell As Range
Dim WrkRng As Range
For Each wkSht In Worksheets
With wkSht
If .Name = "ListLetters" Then
Application.DisplayAlerts = False
Sheets("ListLetters").Delete
End If
End With
Next
Application.DisplayAlerts = True
Set WrkRng = ActiveSheet.UsedRange

For Each cell In WrkRng
For ii = 1 To Len(cell)
If Mid(UCase(cell), ii, 1) Like "[A-Z]" Then
letCount(Asc(Mid(UCase(cell), ii, 1)) - 64) = _
letCount(Asc(Mid(UCase(cell), ii, 1)) - 64) + 1
End If
Next ii
Next cell
Set CopytoSheet = Worksheets.Add
CopytoSheet.Name = "ListLetters"
CopytoSheet.Activate
Range("B1").Resize(26, 1).Value = Application.Transpose(letCount)
With Range("A1").Resize(26, 1)
.Formula = "=char(row()+64)"
.Value = .Value
End With

End Sub


Gord Dibben MS Excel MVP

On Sat, 01 Mar 2008 00:13:49 GMT, "saman110 via OfficeKB.com" <u35670@uwe
wrote:

I have a column with many rows that may have blank cells in between them. how
can I get the instance or count of them?

For example in A column I have;

Q
S
Q
Q
H
H
C

Results should be;

3 Q
1 S
2 H
1 C

saman110,

Use a pivot table. Add the column to both the row area and the data area of
the pivot table. If the data area doesn't automatically select the count
function, go in and change it manully.

Look up using pivot tables in XL's Help.

HTH,

Conan




"saman110 via OfficeKB.com" <u35670@uwe wrote in message
news:807ab5487f8c3@uwe...
I have a column with many rows that may have blank cells in between them.
how
can I get the instance or count of them?

For example in A column I have;

Q
S
Q
Q
H
H
C

Results should be;

3 Q
1 S
2 H
1 C

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200802/1

My column does not always have Alphabet. It may contain words.

saman110 wrote:
I have a column with many rows that may have blank cells in between them. how
can I get the instance or count of them?

For example in A column I have;

Q
S
Q
Q
H
H
C

Results should be;

3 Q
1 S
2 H
1 C

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200803/1