Hi,

You may find it better to do the search up-side-down,
otherwise you might miss out rows.

Also, it may be far more efficient to use excel's
autofilter method to first filter the rows using your del
criteria. Then delete the visible rows all in one go.

You might record a macro setting up the autofilter, then
pressing F5 (goto), special, visible cells only, then
doing the deletion. Then edit the recorded (bad) code to
generalise.

Regards,

Jan Karel Pieterse
Excel TA/MVP

-----Original Message-----
I posted this last week but the only person who showed an
interest
didn't suggest any specific improvements. I was mildly
surprised
because I would have though this was a common use of
Excel VBA.

My data is organised in rows of columns (like a database
table). I
want to look for the first occurrence of a value in a
certain column
and delete that row.

Below is my attempt. In case someone is in the know, I'm
trying to
implement a simplified version of the SQL DELETE command.
Can anyone
suggest improvements to my code?

Private Function DeleteByKey(ExcelRange As Excel.Range, _
ByVal KeyColumn As Long, _
ByVal KeyValue As Variant) As
Boolean

Dim vntArray As Variant
Dim lngRows As Long
Dim lngCounterRows As Long

vntArray = ExcelRange.Value
lngRows = UBound(vntArray, 1)

' Loop through rows and try to match key
For lngCounterRows = 1 To lngRows

If vntArray(lngCounterRows, KeyColumn) = KeyValue Then

' Match
ExcelRange.Rows(lngCounterRows).Delete xlShiftUp

DeleteByKey = True
Exit Function

End If

Next

' No match
DeleteByKey = False

End Function

Public Sub TestDeleteByKey()
If DeleteByKey(Range("Data"), 2, "Hip") Then
MsgBox "Row Successfully deleted."
Else
MsgBox "Failed", vbCritical
End If
End Sub

Many thanks.
.