title: How to remove duplicate values in certain columns without selection in Excel VBS?
body: <p>Assume that this is my data in Excel <a href="http://i.stack.imgur.com/G5zb8.jpg" rel="nofollow">Data containing duplicate values in the first three columns</a>.
  As you can see the values in the first three columns are repeated for a number of rows.</p>
  
  <p>I want to remove the duplicate values in them just like this screenshot 
  <a href="http://i.stack.imgur.com/hOeDs.jpg" rel="nofollow">duplicate values are removed using a macro</a></p>
  
  <p>I decided to use a macro that does this for me automatically and I found this VBS code that removes the duplicate values. What the macro actually does is that it removes the repeating values in the selected area where the cursor is in, so each time the macro runs I have to select the area that I would like the values to be removed. But, what I want is to remove the duplicates from the columns A, B, and C whether or not they are selected and no matter how many rows there are. And, I want it to work on open automatically. </p>
  
  <p>I considered using Range() instead of Selection() e.g. I put something like <code>Set r = Columns("A:C").Select</code> but that didn't work. Is there a way to do this in VBS? </p>
  
  <pre><code>Option Explicit
  
  Private originalValues()
  Private originalRange As String
  
  Sub removeDupes()
   Dim r As Range 'target range
   Dim arr() 'array to hold values
   Dim i As Long, j As Long, k As Long 'loop control
   Dim upper1D As Long, upper2D As Long, lower2D As Long 'array bounds
   Dim s As String 'temp string to compare values 
  
    Set r = Selection.Resize(Cells.SpecialCells(xlLastCell).Row)
  
    If r.Rows.Count = 1 Then Exit Sub 'if the target range is only 1 row then quit
     arr = r.Value 'copy the values in r to the array
  
   'store the values for an undo
  originalValues = r.Value
  originalRange = r.Address
  
  upper1D = UBound(arr) 'get the upper bound of the array's 1st dimension
  upper2D = UBound(arr, 2) 'get the upper bound of the array's 2nd dimension
  lower2D = LBound(arr, 2) 'get the lower bound of the array's 2nd dimension
  
   'loop through 'rows' in the array
  For i = LBound(arr) To upper1D
       'loop through all the 'columns' in the current row
      For j = lower2D To upper2D
          s = arr(i, j) 'record the current array component value in s
           'Check to see if duplicates exists in the target range
          If Application.CountIf(r.Columns(j), s) &gt; 1 _
          And LenB(s) Then
               'Duplicate found: if the end of the array has not ye been reached then
               'loop through the remaining rows for this column, clearing duplicates
              If i &lt; upper1D Then
                  For k = i + 1 To upper1D
                      If arr(k, j) = s Then arr(k, j) = ""
                  Next k
              End If
          End If
      Next j
  Next i
   'copy array back to target range
  r.Value = arr
  Application.OnUndo "Undo remove duplicates", "restoreOriginalValues"
   End Sub
  
   Private Sub restoreOriginalValues()
    Range(originalRange).Value = originalValues
   End Sub
  </code></pre>
  
  <p>Thanks,
  Laleh</p>