CODEWITHSUNDEEP
excelvba
atschoe
atschoe

Reputation: 31

How to compare values in a column?

I have a column of integers with consecutive duplicates.

I'd like to insert a row after each consecutive match.

My values are:

2
2
5
5
10
10
20
20

The code's output is:

2
2
5
5

10
10

20
20

It is working, up until it is differentiating between 2 and 5.

For k = myRange.Rows.Count To 2 Step -1
    If InStrRev(myRange.Cells(k, 1).Value2, vbTextCompare) <> InStrRev(myRange.Cells(k - 1, 1).Value2, vbTextCompare) Then
        Range(myRange.Cells(k, 1).EntireRow, myRange.Cells(k, 1).EntireRow).Insert
    End If
Next k

Where 'myRange' is the range of values to separate.

I tried using myRange.Cells(k,1).Text instead of Value2, and also tried doing a vbBinaryCompare instead of a text compare.

Upvotes: 1

Views: 89

Answers (2)

GMalc
GMalc

Reputation: 2628

Here is a simple example of what you are trying to do.

For k = myRange.Rows.Count To 3 Step -1 'If you have a header and loop to row 2 it will insert a row above row 2
    If Cells(k, 1) <> Cells(k - 1, 1) Then
        Cells(k, 1).EntireRow.Insert
    End If
Next k

Upvotes: 2

jsteurer
jsteurer

Reputation: 244

dim r as range   
Set r = Range("a1")

irow = r.Row
icol = r.Column

Do

If Cells(irow + 1, icol) <> Cells(irow, icol) Then
    Cells(irow + 1, icol).EntireRow.Insert shift:=xlDown
    irow = irow + 2
Else
    irow = irow + 1
End If

Loop While Not Cells(irow, icol).Text = ""

Upvotes: 1

Related Questions