Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If Statement with Wildcards not Working
I'm trying to make delete all rows that contain the text "UASGN" in column J.
I have tried a few different methods based upon research from this site, but I'm having an issue with each one. The one I think I'll use is this: For i = 1 To 672 If Cells(i, "J") = "*UASGN*" Then Rows(i & ":" & i).Select Selection.Delete Shift:=xlUp i = i - 1 ' as we just deleted a row, we should not increase i End If Next i When I use this, it appears to have a problem with the wildcards. If I remove the wildcards and replace with a finite character(s), it works. Thanks. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If Statement with Wildcards not Working
Hi
Try If Cells(i, "J") LIKE "*UASGN*" Then Wkr, JP "cr0375" wrote in message ... I'm trying to make delete all rows that contain the text "UASGN" in column J. I have tried a few different methods based upon research from this site, but I'm having an issue with each one. The one I think I'll use is this: For i = 1 To 672 If Cells(i, "J") = "*UASGN*" Then Rows(i & ":" & i).Select Selection.Delete Shift:=xlUp i = i - 1 ' as we just deleted a row, we should not increase i End If Next i When I use this, it appears to have a problem with the wildcards. If I remove the wildcards and replace with a finite character(s), it works. Thanks. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If Statement with Wildcards not Working
Try the below
For i = 672 To 1 Step -1 If Cells(i, "J") Like "*UASGN*" Then Rows(i & ":" & i).Delete Next i If this post helps click Yes --------------- Jacob Skaria "cr0375" wrote: I'm trying to make delete all rows that contain the text "UASGN" in column J. I have tried a few different methods based upon research from this site, but I'm having an issue with each one. The one I think I'll use is this: For i = 1 To 672 If Cells(i, "J") = "*UASGN*" Then Rows(i & ":" & i).Select Selection.Delete Shift:=xlUp i = i - 1 ' as we just deleted a row, we should not increase i End If Next i When I use this, it appears to have a problem with the wildcards. If I remove the wildcards and replace with a finite character(s), it works. Thanks. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If Statement with Wildcards not Working
Another way to do it without looping through all the cells in the range is to
use .Find(). Then you just find, delete, find, delete, ... until there isn't any more left. In code: Option Explicit Sub testme() Dim wks As Worksheet Dim FoundCell As Range Dim FindWhat As String Set wks = ActiveSheet FindWhat = "uasgn" With wks.Range("J:j") Do Set FoundCell = .Cells.Find(what:=FindWhat, _ after:=.Cells(1), _ LookIn:=xlValues, _ lookat:=xlPart, _ searchorder:=xlByRows, _ searchdirection:=xlNext, _ MatchCase:=False) If FoundCell Is Nothing Then 'they're all gone, get out of the loop Exit Do Else 'delete the entire row FoundCell.EntireRow.Delete End If Loop End With End Sub The xlpart is important (like your *uasgn* comparison). I used matchcase:=false. You may not want that. Another way to do it manually if you don't want to run the macro. Apply data|filter|autofilter to column J: filter to show the cells/rows that contain UASGN. Delete those visible rows (you may need to select the range, hit F5, special|visible cells only, then delete them.) Another way (depending on the version of excel you're using): Select column J Edit|Find what: uasgn (in values and not matching the entire cell) Hit Find All (if you have that button on the Find dialog) Select one of the lines in that listbox and hit ctrl-a to select them all. Then delete the rows that are still selected. (rightclick on one and choose delete, entire row) cr0375 wrote: I'm trying to make delete all rows that contain the text "UASGN" in column J. I have tried a few different methods based upon research from this site, but I'm having an issue with each one. The one I think I'll use is this: For i = 1 To 672 If Cells(i, "J") = "*UASGN*" Then Rows(i & ":" & i).Select Selection.Delete Shift:=xlUp i = i - 1 ' as we just deleted a row, we should not increase i End If Next i When I use this, it appears to have a problem with the wildcards. If I remove the wildcards and replace with a finite character(s), it works. Thanks. -- Dave Peterson |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If Statement with Wildcards not Working
thanks both of you
"JP Ronse" wrote: Hi Try If Cells(i, "J") LIKE "*UASGN*" Then Wkr, JP "cr0375" wrote in message ... I'm trying to make delete all rows that contain the text "UASGN" in column J. I have tried a few different methods based upon research from this site, but I'm having an issue with each one. The one I think I'll use is this: For i = 1 To 672 If Cells(i, "J") = "*UASGN*" Then Rows(i & ":" & i).Select Selection.Delete Shift:=xlUp i = i - 1 ' as we just deleted a row, we should not increase i End If Next i When I use this, it appears to have a problem with the wildcards. If I remove the wildcards and replace with a finite character(s), it works. Thanks. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If Statement with Wildcards not Working
Helpful, Thanks.
"Jacob Skaria" wrote: Try the below For i = 672 To 1 Step -1 If Cells(i, "J") Like "*UASGN*" Then Rows(i & ":" & i).Delete Next i If this post helps click Yes --------------- Jacob Skaria "cr0375" wrote: I'm trying to make delete all rows that contain the text "UASGN" in column J. I have tried a few different methods based upon research from this site, but I'm having an issue with each one. The one I think I'll use is this: For i = 1 To 672 If Cells(i, "J") = "*UASGN*" Then Rows(i & ":" & i).Select Selection.Delete Shift:=xlUp i = i - 1 ' as we just deleted a row, we should not increase i End If Next i When I use this, it appears to have a problem with the wildcards. If I remove the wildcards and replace with a finite character(s), it works. Thanks. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If Statement with Wildcards not Working
Helpful, thanks!
"Dave Peterson" wrote: Another way to do it without looping through all the cells in the range is to use .Find(). Then you just find, delete, find, delete, ... until there isn't any more left. In code: Option Explicit Sub testme() Dim wks As Worksheet Dim FoundCell As Range Dim FindWhat As String Set wks = ActiveSheet FindWhat = "uasgn" With wks.Range("J:j") Do Set FoundCell = .Cells.Find(what:=FindWhat, _ after:=.Cells(1), _ LookIn:=xlValues, _ lookat:=xlPart, _ searchorder:=xlByRows, _ searchdirection:=xlNext, _ MatchCase:=False) If FoundCell Is Nothing Then 'they're all gone, get out of the loop Exit Do Else 'delete the entire row FoundCell.EntireRow.Delete End If Loop End With End Sub The xlpart is important (like your *uasgn* comparison). I used matchcase:=false. You may not want that. Another way to do it manually if you don't want to run the macro. Apply data|filter|autofilter to column J: filter to show the cells/rows that contain UASGN. Delete those visible rows (you may need to select the range, hit F5, special|visible cells only, then delete them.) Another way (depending on the version of excel you're using): Select column J Edit|Find what: uasgn (in values and not matching the entire cell) Hit Find All (if you have that button on the Find dialog) Select one of the lines in that listbox and hit ctrl-a to select them all. Then delete the rows that are still selected. (rightclick on one and choose delete, entire row) cr0375 wrote: I'm trying to make delete all rows that contain the text "UASGN" in column J. I have tried a few different methods based upon research from this site, but I'm having an issue with each one. The one I think I'll use is this: For i = 1 To 672 If Cells(i, "J") = "*UASGN*" Then Rows(i & ":" & i).Select Selection.Delete Shift:=xlUp i = i - 1 ' as we just deleted a row, we should not increase i End If Next i When I use this, it appears to have a problem with the wildcards. If I remove the wildcards and replace with a finite character(s), it works. Thanks. -- Dave Peterson |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If Statement with Wildcards not Working
Hi,
You may also try this. Select the column and press Ctrl+F. In the find what box, type UASGN and then click on find All. Now press Ctrl+A to highlight all the cell which have UASGN. You may now delete them -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "cr0375" wrote in message ... I'm trying to make delete all rows that contain the text "UASGN" in column J. I have tried a few different methods based upon research from this site, but I'm having an issue with each one. The one I think I'll use is this: For i = 1 To 672 If Cells(i, "J") = "*UASGN*" Then Rows(i & ":" & i).Select Selection.Delete Shift:=xlUp i = i - 1 ' as we just deleted a row, we should not increase i End If Next i When I use this, it appears to have a problem with the wildcards. If I remove the wildcards and replace with a finite character(s), it works. Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF Statement not working | Excel Worksheet Functions | |||
Wildcards in IF statement | Excel Discussion (Misc queries) | |||
Wildcards with numbers....* & ? not working | Excel Worksheet Functions | |||
Autofilter Wildcards Not Working on Number Column | New Users to Excel | |||
Autofilter Wildcards Not Working on Number Column | Excel Worksheet Functions |