Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete row from range using key column
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" wrote in message ... 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" wrote in message m... 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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete row from range using key column
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 "onedaywhen" wrote in message om... 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" wrote in message ... 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" wrote in message m... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Delete some column headings from an autofiltered range. | Excel Worksheet Functions | |||
How to Delete blanks between a range and populate only the names inthe given range | Excel Discussion (Misc queries) | |||
Copy column header to next column, delete & delete every nth colum | New Users to Excel | |||
How can I insert and delete rows within a locked cell range column | Excel Worksheet Functions | |||
How to Delete a Range in Closed Workbook (to Replace Delete Query) | Excel Discussion (Misc queries) |