Home |
Search |
Today's Posts |
#1
|
|||
|
|||
What is a function in VBA EXCEL witch finds a string like "not" in cell and then deletes a row with this cell?
Hello,
I have a problem with finding a string in a selection range and than deletes row including this string. For example: I have in column A: ________A________ 1 | This is a cat | 2 | This is not a cat | 3 | This is not a dog | 4 | This is a fly | ........................... 40| This is not a horse | 41| etc.. | I want to find cells which includs string "not" and delete a row with this string. How can I do this in a simply way? Thanks for response. |
#2
|
|||
|
|||
What is a function in VBA EXCEL witch finds a string like "not" in cell and then deletes a row with this cell?
Hi
You can use AutoFilter in the Data menu Use Custom and contains Then with a cell in the column Active press Ctrl * to select the whole range and then Alt ; to select onlt the visible cells Then EditDelete Row With code (A1 is the header cell) Sub Delete_with_Autofilter() Dim DeleteValue As String Dim rng As Range DeleteValue = "*not*" ' This will delete the rows with "ron" in the Range("A1:A100") With ActiveSheet .Range("A1:A100").AutoFilter Field:=1, Criteria1:=DeleteValue With ActiveSheet.AutoFilter.Range On Error Resume Next Set rng = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _ .SpecialCells(xlCellTypeVisible) On Error GoTo 0 If Not rng Is Nothing Then rng.EntireRow.Delete End With .AutoFilterMode = False End With End Sub -- Regards Ron de Bruin http://www.rondebruin.nl wrote in message oups.com... Hello, I have a problem with finding a string in a selection range and than deletes row including this string. For example: I have in column A: ________A________ 1 | This is a cat | 2 | This is not a cat | 3 | This is not a dog | 4 | This is a fly | .......................... 40| This is not a horse | 41| etc.. | I want to find cells which includs string "not" and delete a row with this string. How can I do this in a simply way? Thanks for response. |
#3
|
|||
|
|||
What is a function in VBA EXCEL witch finds a string like "not" in cell and then deletes a row with this cell?
Another possible answer in programming group.
There is no need to multi-post...most contributors read all the Excel groups. This question is obviously a VBA (Programming) one so posting it as well in WorksheetFunctions is also off topic an unnecessary -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England www.nickhodge.co.uk HIS wrote in message oups.com... Hello, I have a problem with finding a string in a selection range and than deletes row including this string. For example: I have in column A: ________A________ 1 | This is a cat | 2 | This is not a cat | 3 | This is not a dog | 4 | This is a fly | .......................... 40| This is not a horse | 41| etc.. | I want to find cells which includs string "not" and delete a row with this string. How can I do this in a simply way? Thanks for response. |
#4
|
|||
|
|||
What is a function in VBA EXCEL witch finds a string like "not" in cell and then deletes a row with this cell?
And yet another in comp.apps.spreadsheets
This question appears to have been separately posted all over. Jerry Nick Hodge wrote: Another possible answer in programming group. There is no need to multi-post...most contributors read all the Excel groups. This question is obviously a VBA (Programming) one so posting it as well in WorksheetFunctions is also off topic an unnecessary |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|