counting cells (office 2003)

Topic

New Reply

Hi all,
I am trying to loop through a column of numbers in which some are the same number and some are different numbers. (Example: 775,775,775,776,777). I need to determine how many times the number changes. So for the example, there is a column with 5 numbers. cells A2 through A6 contain the values. Since 775 occurs three times, I only want to count it once. So at the end of the loop I should get a value of 3. I can do the basic loop with out any problem but do not understand how to set up the criteria of whether or not to count it. Thanks for any help.

Kevin

Reply | Quote

Replies

If you want to count the unique items in a list, you can use the following array formula (confirm formula with ctrl+shift+enter):

=SUM(1/COUNTIF(A2:A6,A2:A6))

Reply | Quote

Thank You,

Since I am trying to do perform some tasks in the on open event, is there a way to incorporate this in a VBA loop? This is a situation where there may be 6 cells in the column or there may be 300 cells in the column. I was hoping to use vba to determine the number of rows with data by using …

…Sheets(“qrySSTARDataforHardCopyReport”).Select
Range(“A2”, “A1000”).Select

‘count rows for loop
myCount = Application.CountA(Selection)…

… and then use the myCount variable to determine how many different values were in a specific column within that original range.

Thank You
Kevin

Reply | Quote

You can use something like this:

Dim col As New Collection
Dim r As Long
Dim m As Long

On Error Resume Next

With Worksheets(“qrySSTARDataforHardCopyReport”)
m = .Cells(65536, 1).End(xlUp).Row
‘ Loop through column A
For r = 2 To m
‘ Don’t count blanks
If Not .Cells(r, 1) = “” Then
col.Add .Cells(r, 1), “A” & .Cells(r, 1)
End If
Next r
End With
Debug.Print col.Count
Set col = Nothing

Note that the code doesn’t select the worksheet or any cell.
The code uses the fact that the key of items in a collection must be unique. If the code tries to add an item that has been added before, the On Error Resume Next line suppresses the error message and skips to the next line.

Reply | Quote

My previous post count the unique items in a list. The code counts the number of times the value changes in a range. Currently the results are displayed as a message box. The code assumes no empty cells are in the A column.

The array formula, for the following data results in 3, while the code reults in a value of 8.

775
775
775
776
777
775
775
775
776
777
775
775
775
776

Sub countChanges()
Dim cCell As Range, cCount As Long, last As Variant
Dim current As Variant, rCount As Long, i As Long

rCount = Cells(65536, 1).End(xlUp).Row – 2

cCount = 1
last = Cells(rCount, 1)

For i = rCount To 2 Step -1
current = Cells(i, 1)
If current = last Then
cCount = cCount
last = current
Else
cCount = cCount + 1
last = current
End If
Next

MsgBox (cCount)

End Sub

Reply | Quote

Hi, thanks for the reply, I latched onto your concept for counting but do not understand how your code points to column A. If I wanted to apply this to other columns, what parts of the require changing. I have played with the various lines of code trying to point to different columns but am not changing all that need to be changed because my results are all over the place.

Thank You

Kevin

Reply | Quote

The A column is referenced using Cells(Row,Column) instead of using the Range(“A1”) method. I’ve highlighted all references to the A column. Change the 1s to the column number you want to reference. B=2, C=3, etc.

Sub countChanges()
Dim cCell As Range, cCount As Long, last As Variant
Dim current As Variant, rCount As Long, i As Long

rCount = Cells(65536, 1).End(xlUp).Row - 2

cCount = 1
last =Cells(rCount, 1)


For i = rCount To 2 Step -1
current = Cells(i, 1)
If current = last Then
cCount = cCount
last = current
Else
cCount = cCount + 1
last = current
End If
Next

MsgBox (cCount)

End Sub 

Reply | Quote

Thank You very much

Reply | Quote