Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
REPOST: Find a row and delete it
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
REPOST: Find a row and delete it
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. . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
REPOST: Find a row and delete it
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 ---------------------------------------------------------------------------- "Jan Karel Pieterse" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
REPOST: Find a row and delete it
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 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 ---------------------------------------------------------- ------------------ "Jan Karel Pieterse" wrote in message ... 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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Repost of find and move values<0 | Excel Worksheet Functions | |||
find and delete | Excel Worksheet Functions | |||
Find and delete | Excel Worksheet Functions | |||
macro to delete lines based on a value - Repost | Excel Discussion (Misc queries) |