Solved

# Check whether a value exists in column using Excel VBA

Posted on 2012-09-05
453 Views
I am iterating through a column of data getting the value of each cell, what I need to do is check whether the cell value exists within another column. If it does I would like to focus on the row and offer the option 'Yes/No' to delete the cell.
0
Question by:Blowfelt82

Author Comment

One thing to mention is that the value I am comparing is a filename without extension, and the column I am checking for the value in has a known extension.
0

LVL 18

Accepted Solution

Hi

Try

``````Sub kTest()

Dim k, ka, i As Long, txt As String, n As String
Dim a() As String, j As Long
Const SearchCol     As Long = 2
Const FileNameCol   As Long = 10
Const StartRow      As Long = 2
Const ShtName       As String = "Sheet1"
'// End
k = Intersect(Worksheets(ShtName).UsedRange, Worksheets(ShtName).Columns(SearchCol)).Value2
ka = Intersect(Worksheets(ShtName).UsedRange, Worksheets(ShtName).Columns(FileNameCol)).Value2

With CreateObject("scripting.dictionary")
.comparemode = 1
For i = 1 To UBound(ka, 1)
If Len(ka(i, 1)) Then _
n = Left\$(ka(i, 1), InStrRev(ka(i, 1), ".") - 1)
.Item(n) = Empty
Next
For i = 1 To UBound(k, 1)
If .exists(k(i, 1)) Then
txt = IIf(Len(txt), txt & ",a" & StartRow + i - 1, "A" & StartRow + i - 1)
If Len(txt) > 245 Then
j = j + 1: ReDim Preserve a(1 To j)
a(j) = txt: txt = vbNullString
End If
End If
Next
If Len(txt) Then
j = j + 1: ReDim Preserve a(1 To j)
a(j) = txt: txt = vbNullString
End If
If j Then
If MsgBox("Do you want to delete the matched cells?", vbYesNo) = 6 Then
For i = j To 1 Step -1
'                    Range(a(j)).EntireRow.Delete    'delete rows
Worksheets(ShtName).Columns(SearchCol).Range(a(j)).Delete -4162        'delete cells
Next
End If
End If
End With

End Sub
``````

Kris
0

## Featured Post

### Suggested Solutions

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.