need VBA Selecting cells Containing specific Characters

[pisdukas]

Hello, Forum,

I am unable to find a VBA which would select cells which contain specific characters in a range. Could some give a hand?


Thank you for taking the time.

 

[Twollaston]

*removed duplicate posting*

 

[Twollaston]

Updated for "*WHI-*" with dash added before astrisk\

Sub DeleteRowss()
'
' DeleteRowss Macro
'




'
    Sheets("Sheet1").Select




    ActiveSheet.Range("A1").AutoFilter Field:=1, Criteria1:="*WHI-*", Operator:=xlFilterValues
    
    Range("A1").Select
    ActiveCell.Offset(1, 0).Rows("1:1").EntireRow.Select
    
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Delete Shift:=xlUp
    
    Worksheets("sheet1").AutoFilterMode = False




End Sub

Worked in Test File

Excel 2010
	A
1	item_sku
2	HOD-UNI-0001-WHI
3	HOD-UNI-0001-BLA-S
4	HOD-UNI-0001-BLA-M
5	HOD-UNI-0001-BLA-L
6	HOD-UNI-0001-BLA-XL
7	HOD-UNI-0001-BLA-XXL
8	HOD-UNI-0001-ASH-S
9	HOD-UNI-0001-ASH-M
10	HOD-UNI-0001-ASH-L
11	HOD-UNI-0001-ASH-XL
12	HOD-UNI-0001-ASH-XXL
13	HOD-UNI-0002-WHI
14	HOD-UNI-0002-BLA-S
15	HOD-UNI-0002-BLA-M
16	HOD-UNI-0002-BLA-L
Sheet1

 

[Rick Rothstein]

Sorry, my bad. all of these cells supposed to remain. I have fixed the colors:https://rokastestas.s3.eu-west-3.amazonaws.com/sample2.xlsm

Here is another macro for you to try...

Sub DeleteRowsWithWHI()
  On Error Resume Next
  Application.ScreenUpdating = False
  With Range("A2", Cells(Rows.Count, "A").End(xlUp))
    .Value = Evaluate("IF(ISNUMBER(SEARCH(""-WHI-""," & .Address & ")),""""," & .Address & ")")
    .SpecialCells(xlBlanks).EntireRow.Delete
  End With
  Application.ScreenUpdating = True
  On Error GoTo 0
End Sub

 

[Twollaston]

Here is another macro for you to try...

    .Value = Evaluate("IF(ISNUMBER(SEARCH(""-WHI-""," & .Address & ")),""""," & .Address & ")")
    [COLOR=#333333].SpecialCells(xlBlanks).EntireRow.Delete[/COLOR]
     
  
End Sub

Rick this is very cool, and so fast.

 

[Rick Rothstein]

Rick this is very cool, and so fast. This seems much faster than my macro. If this was an extreme amount of data, will this one work faster than a normal filter and delete?
Just wondering cause I have some data I may test that with

I don't know how it compares speedwise to a normal filter as I have never tested it... it was just something that came to me several years ago and I have been posting it as an alternative ever since.

 

[Twollaston]

I don't know how it compares speedwise to a normal filter as I have never tested it... it was just something that came to me several years ago and I have been posting it as an alternative ever since.

Nice, I may test it out with some of my work reports, cause i'm looking for any kind of speed boost I can get. Thanks

 

[pisdukas]

Here is another macro for you to try...

Sub DeleteRowsWithWHI()
  On Error Resume Next
  Application.ScreenUpdating = False
  With Range("A2", Cells(Rows.Count, "A").End(xlUp))
    .Value = Evaluate("IF(ISNUMBER(SEARCH(""-WHI-""," & .Address & ")),""""," & .Address & ")")
    .SpecialCells(xlBlanks).EntireRow.Delete
  End With
  Application.ScreenUpdating = True
  On Error GoTo 0
End Sub

I am having trouble implementing your code into my VBA, so I made that it would run you macro within my macro. But it replaces blank cells with 0 and replaces "WHI" with "ASH".

https://i.ibb.co/zZgjDTV/vba.jpg

Sub HOD_01()
'
' HOD_01 Macro
'


'
Set wkb = Workbooks.Open(Filename:="D:\Parent.csv")
  
     Columns("B:B").Select
    Selection.Replace What:="TEE-VYR", Replacement:="HOD-UNI", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        Selection.Replace What:="WHI", Replacement:="ASH", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        Range("B4:B1048576").Select
        Range("B1048576").Activate
        Selection.Copy
        Selection.Replace What:="TEE", Replacement:="HOD", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        
        Application.Run "'sample2.xlsm'!Delete"
        
        ThisFile = "HOD_01"
       Application.ScreenUpdating = True
ActiveWorkbook.SaveAs Filename:="D:\" & ThisFile & ".txt", FileFormat:=xlText
Workbooks("HOD_01.txt").Close SaveChanges:=False
End Sub


Sub Delete()
  On Error Resume Next
  Application.ScreenUpdating = False
  With Range("B2", Cells(Rows.Count, "B").End(xlUp))
    .Value = Evaluate("IF(ISNUMBER(SEARCH(""-WHI-""," & .Address & ")),""""," & .Address & ")")
    .SpecialCells(xlBlanks).EntireRow.Delete
  End With
  Application.ScreenUpdating = True
 
End Sub