Removing consecutive duplicate values from CSV in Excel Visual Basic

Question

In an Excel 2007 VB Macro, I'm trying to do is take a comma separate String, split it, and then reduce it, removing duplicate consecutive values. So "2,2,2,1,1" would become "2,1", or "3,3,3,2,3,3,3" would become "3,2,3".

It looks like it should work, but when it gets to the "If currentVal.equals(prevVal) = False Then", it his a runtime error 424, 'Object required'.

It's been forever since I did any VB, and that was VP6.

Sheets("Sheet1").Select
Range("I1").Select

Dim data() As String
Dim currentVal, prevVal As String
Dim output As String
Dim temp As Boolean

Do Until Selection.Value = ""
        data = Split(Selection, ",")
        output = ""
        prevVal = ""
        For Each elem In data
            currentVal = CStr(elem)
            If currentVal.equals(prevVal) = False Then
            output = output + elem + ","
            End If
        Next elem
        Selection.Value = output
        Selection.Offset(1, 0).Select
Loop

score 3 · Accepted Answer · answered Sep 14 '11 at 23:35

There's a few problems. First, you can't use:

Dim currentVal, prevVal As String

You must use:

Dim currentVal as String
Dim prevVal As String

or:

Dim currentVal as String, prevVal as String

...as you can't shortcut types in VBA unfortunately. Secondly, strings aren't objects in VBA so there's no .equals (or any other method). You want:

If currentVal <> prevVal Then

Lastly, you need to set prevVal at the end of your loop or your code won't work as expected.

EDIT Here's some working code:

Dim data() As String
Dim currentVal As String, prevVal As String
Dim output As String
Dim temp As Boolean

Do Until Selection.Value = ""
        data = Split(Selection, ",")
        output = ""
        prevVal = ""
        For Each elem In data
            currentVal = CStr(elem)
            If currentVal <> prevVal Then
                output = output + elem + ","
            End If
            prevVal = currentVal
        Next elem
        Selection.Value = output
        Selection.Offset(1, 0).Select
Loop

Yea, realized the last point, fixed it. Also got the currentVal = prevVal from an IRC chan, thanks. However, it looks (to me) that String should be an Object, and have the .equals() method http://msdn.microsoft.com/en-us/library/system.string.equals%28v=vs.80%29.aspx Of course, this is VBA, not VB.NET, so that might make all the difference. — Drizzt321, Sep 14 '11 at 23:39
Oh yah, it makes all the difference. VBA is based on VB6 and has little in common with VB.NET outside of small syntax similarities. Data types such as string, boolean, integer, long, etc. aren't objects in VBA but rather hold the actual data value in memory so there are no associated properties or methods and they can be compared and operated on directly. String.equals() is needed in languages where strings are an object as the variable points to a memory address rather than holding the actual value. — Banjoe, Sep 14 '11 at 23:55

brettdj · Answer 2 · 2011-09-15T03:39:12.747

I’d suggest using a variant array with a regular expression to maximise the efficiency and speed of your approach. Something like this

Update: Picking up on my own advice elsewhere the code now test for more than 1 cell before applying the variant array

   Sub Clear()
    Dim ws As Worksheet
    Dim rng1 As Range
    Dim X
    Dim lngRow As Long
    Dim objRegex
    Set objRegex = CreateObject("vbscript.regexp")
    Set ws = Sheets("Sheet1")
    Set rng1 = ws.Range(ws.[i1], ws.Cells(Rows.Count, "I").End(xlUp))
    With objRegex
        .Global = True
        .Pattern = "(\d)(,(\1))+"
        If rng1.Cells.Count > 1 Then
            X = rng1
            For lngRow = 1 To UBound(X)
                X(lngRow, 1) = .Replace(X(lngRow, 1), "$1")
            Next lngRow
            rng1 = X
        Else
            rng1.Value = .Replace(rng1.Value, "$1")
        End If
    End With
End Sub

score 1 · Answer 3 · edited May 23 '17 at 12:08

1

You could use a dictionary object especially since you are moving the numbers to a text file it doesn't matter that they are not treated as numbers per se. See this question

edited May 23 '17 at 12:08

Community

1
1

answered Sep 15 '11 at 01:02

Brad

11,934
4
45
73

Yea, I ended up modifying the code to use a Dictionary, and then outputting the aggregated data to a 2nd sheet. I'm actually pretty proud of myself for getting it done in only 4-5 hours! First time doing VBA. – Drizzt321 Sep 15 '11 at 16:56
Cool. Yeah those dictionary objects are pretty handy. Way to go! – Brad Sep 15 '11 at 17:53

Removing consecutive duplicate values from CSV in Excel Visual Basic

3 Answers3