0

so I have a single column on a worksheet. it looks like this:

a,a,a,b,b,b,c,c,c,c,d,d,d,d,e,e,e,e etc

(yes its a column. this thing doesn't like returns..)

now if you click on the column, and select GoTo Special>Column Differences

it should select the first.. a,b,c,d,e cells

but what you get is the cell in ALL? rows after A is selected.

This does it for excel 2003, and 2007

What Am I doing wrong?

KevinDeus
  • 317

2 Answers2

1

GoTo Special>Column Differences operates on the selected range. I'm not sure why you thought it would figure out how to iterate over the column.

If you interrogate the value of a range (like your column) the value is the first cell in the range. Therefore, the cells with values not equal to a get selected as being different, which is what I get.

Update: This sub should do what you expect:

' Selects changed cells in a single column.
' This sub will *not* work on multiple column selections
Public Sub SelectColumnDifferences()
    Dim c As Range
    Dim different As Range

    Set different = Selection.Cells(1)
    For Each c In Selection
        If c.Value <> different.Areas(different.Areas.Count).Value Then
            Set different = Union(different, c)
        End If
    Next c

    different.Select

    Set different = Nothing
    Set c = Nothing
End Sub
DaveParillo
  • 14,761
1

This alternate method wound up working just as well..

How do I get the distinct/unique values in a column in Excel?

Community
  • 1
KevinDeus
  • 317