REPOST: Find a row and delete it

[onedaywhen]

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.

 

[Jan Karel Pieterse]

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

 

[Ken Wright]

Hi Jan. He would though end up deleting all rows that contained his criteria, and not as I think
he is suggesting, delete only the first instance of the criteria. (Assuming he has articulated
his problem correctly of course)

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL2K & XLXP

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------

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,

<snip>

 

[Jan Karel Pieterse]

Hi,

Oops, you're right of course.

In that case, I see little improvement opportunities,
short of using the MATCH worksheet function to quickly
find the first match and act accordingly. It would
probably beat looping through the rows.

Regards,

Jan Karel Pieterse
Excel TA/MVP

-----Original Message-----
Hi Jan. He would though end up deleting all rows that

contained his criteria, and not as I think

he is suggesting, delete only the first instance of the

criteria. (Assuming he has articulated