title: Preserving distinct values VBA excel - deleting all the rest
body: <p>I have a column with random values that has repeats data in a certain format:</p>
  
  <p>Column 1</p>
  
  <pre><code>A
  A
  A
  A
  B
  B
  C
  C
  C
  A
  A
  D
  D
  E
  F
  F
  F
  G
  ...
  </code></pre>
  
  <p>I tried using </p>
  
  <pre><code> Dim i As Integer
      Dim noRows As Integer
  
  
  'count rows
  noRows = Range("B2:B10000").Rows.Count
  
  'delete row entries that are duplicates..
  For i = 1 To noRows
      If Range("H2").Cells(i + 1, 1) = Range("H2").Cells(i, 1) Then
      Range("H2").Cells(i + 1).Resize(1, 2).Clear
      End If
  Next i
  </code></pre>
  
  <p>Which I quickly realised would not work at all.</p>
  
  <p>how could I write a code so the output would be:</p>
  
  <p>Column 1</p>
  
  <pre><code>A 
  [..]
  [..]
  [..] 
  B 
  [..] 
  C 
  [..] 
  [..] 
  [..] 
  A 
  D 
  [..] 
  [..] 
  E 
  F 
  [..] 
  [..] 
  G 
  ...
  </code></pre>
  
  <p>Where the [..]s are nulls or zero</p>
  
  <p>Disregard the second "66.63" field which is green, it should be red</p>
  
  <p><a href="https://i.stack.imgur.com/Zh8a7.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/Zh8a7.jpg" alt="Disregard the second &quot;66.63&quot; field which is green, it should be red"></a></p>