search for (but not delete) first duplicate value

**bauerbach** · 06-03-2012, 11:06 PM

This is to locate a reference cell. The sheet has unformatted data at the top, and then a clean version of the data (blank rows removed) at the bottom. The row labels are unique, but they repeat.

What I would like to do is to create a loop that runs down column B... in garbled high level language (more c++ than vba because its what I know) something like.

for i=1, i<ws.usedrange.rows, i++
{
if (find (ws.row(i+1).value in rows(1,i)))
break
}
msgbox "first duplicate value found in row"&(i+1)

The idea is that it starts off comparing row 2 to rows 1:1, if it is not found in that range, it loops, then it compares row 3 to rows 1:2, and so on. When it first finds a duplicate in the previous range, it breaks the loop and (i+1) now equals the row of the duplicate value.

...

I am trying to learn VBA right now, so I realize my language is terrible, my project outpaces my learning, the functions and syntax are giving me trouble, and its been years since I did C++, not that it translates in every way either.

Any guidance you can give...

**AlvaroSiza** · 06-04-2012, 12:50 AM

I'm not sure this will line up exactly with your query, but given your background, I would think this should get you going in the right direction.

Assuming a source range in Column A with no blanks and a range to be searched in Column B:

Option Explicit

Sub FindIt()

Dim rngCell As Range
Dim rngSource As Range
Dim rngFound As Range

Set rngSource = ActiveSheet.Range("A1", Range("A1").End(xlDown))

   With Application
      .ScreenUpdating = False
      .EnableEvents = False
   End With

      For Each rngCell In rngSource
         Set rngFound = ActiveSheet.Columns(2).Find(What:=rngCell.Value, LookIn:=xlValues, lookat:=xlWhole)
         MsgBox rngFound.Address
         Exit For
      Next rngCell

   With Application
      .EnableEvents = True
      .ScreenUpdating = True
   End With

End Sub

search for (but not delete) first duplicate value

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

search for (but not delete) first duplicate value

Re: search for (but not delete) first duplicate value

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions