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,583
  • so its intended functionality is just to select all columns that are different than the selected cell?

    hmmmm. apparently so.. as i see that mixing up the list (to read a,b,c,a,b,c, etc..) selects all cells that are not a..

    so it does appear to iterate through all the values. apparently it didn't have as much functionality as I thought.

     – KevinDeus Oct 06 '09 at 18:08
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
  • That will end up displaying the unique values, but neither solutions on that page will select them in place. It is possible to write a simple macro to do that. – DaveParillo Oct 06 '09 at 19:53