Delete row from range using key column

[onedaywhen]

What is the best (quickest, most efficient) way of deleting a row from
a range by matching a value in a key column? For example, my range is
A1:C10 and I want to delete the row which has the value "Pies" in the
first column, so if A4="Pies" I want to delete A4:C4 by shifting cells
A5:C10 up (I'm unconcerned about cells A11:C65536; they can also shift
up or remain where they are).

My current approach (code below) is to read the range into a variant
as an array, loop through the 'rows' in the array and if I find a
match in the key 'column' I use the Delete method on the range.

I wondered if there is a better way of doing this e.g. somehow remove
the 'row' from the array, append a blank 'row' and read the values
back to the range?

Please note that in reality I'm matching one or more columns and the
ranges are a lot larger than 10 rows! Many thanks.

Here's my existing 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("A1:C10"), 1, "Pies") Then
MsgBox "Success"
End If
End Sub

 

[Otto Moehrbach]

I think you want to do something different from what you wrote. But if you
just want to delete all the rows that have "Pies" in Column A, then use the
Data - AutoFilter command in the worksheet (no VBA required). Select "Pies"
in Column A. All the rows with "Pies" in Column A will then be clumped into
a group at the top of your sheet. Delete all those rows. Click on Data -
AutoFilter to get out of the filter mode. Done.
Post back if you needed something else. HTH Otto

 

[onedaywhen]

Thanks Otto, I'm posting back because I think I *do* need something
else.

What I'm trying to do (why do people always seem to say this in their
*second* post?) is to simulate the SQL DELETE command, which of course
I can't use in Excel (and anyhow I'm working with the ThisWorkbook
object i.e. it's an open workbook and I'd fall foul of the ADO memory
leak bug). So I'm having to roll my own DELETE routine.

I definitely need to do this in VBA at run-time so I assumed the
autofilter is of no use to me... but I don't know for sure. Do you
know how I could use the autofilter in code to my advantage? Please
bear in mind the range is on a hidden worksheet.

Something else for me to consider is sorting the range. But I don't
know if the overhead is worth it. Is it better to sort the range or
the array, considering that I'd have to restore the range to its
original sort order?

My code as posted does the job but I too thought I may want to do
something different from what I wrote. Do you know what that might be?

Many thanks for any help.

 

[Otto Moehrbach]

Now I definitely don't know what you want to do. If you wish, send me a
small file with a sample of what you have to begin with, clearly show what
you want Excel to do to it, and clearly show, by example, what you want to
have when you are finished.
Yes, you can access the built-in filter utility by code. You can also
perform the filter function (find and delete) by code without using the
built-in filter utility, although it is usually faster if the code accesses
the built-in utility.
Re the hidden sheet. If what you want to do requires that the sheet be
visible, then the code can easily make it visible, do its thing, then hide
the sheet again without the user being aware that any of these actions have
taken place.
Sorting. Whatever you want to do regarding sorting (sort, do this,
return to original order, whatever), can be easily done by code.
Do at start-up. Easy to do.
If you email me direct, remove "nospam" from my address. HTH Otto