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.

 

[Joe4]

Welcome to the Board!

Can you be a little more specific?
Maybe provide an actual example of exactly what you are trying to do (and why the built-in "Find" functionality in Excel won't work?)

 

[pisdukas]

Hey, Joe

Thanks.


I have a "master" table with about 25 000 rows. With the help of VBA I create other 16 tables with a click of a button. Now I need to delete certain rows based on a range A:A and cells which contain characters "WHI" .


Here is the sample file https://rokastestas.s3.eu-west-3.amazonaws.com/sample.xlsm


I want to delete rows which contains value "WHI", I have highlighted it.

 

[Twollaston]

I want to delete rows which contains value "WHI", I have highlighted it.

You can try this one

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

Put your sheet name where it says sheet 1

 

[pisdukas]

Thanks for the reply, for some reason it doesnt work

 

[pisdukas]

You can try this one

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

Put your sheet name where it says sheet 1

Thanks for the reply, for some reason it doesnt work​

​

 

[Twollaston]

Thanks for the reply, for some reason it doesnt work​

Go to the code in vba and have your excel sheet open, put your cursor inside the vba code some where, press f8 to step through the code 1 line at a time and watch it work. Then let me know where you have the error and what is the error message

if the error is referencing a line then copy and paste that line back here

or you can send me a copy of your data and I can fix the code to work on it.

The code worked for me in the test file you sent, it deleted all the rows where the A cell contained WHI

 

[pisdukas]

Go to the code in vba and have your excel sheet open, put your cursor inside the vba code some where, press f8 to step through the code 1 line at a time and watch it work. Then let me know where you have the error and what is the error message

if the error is referencing a line then copy and paste that line back here

or you can send me a copy of your data and I can fix the code to work on it.

The code worked for me in the test file you sent, it deleted all the rows where the A cell contained WHI

I just found out a mistake of mine. All of the cells Containing "WHI" has an ending "..WHI-S", "...WHI-M", "...WHI-L" etc. Exept one type of cells containig "WHI" has no additional ending. And I need to keep those cells. Here is another smaple I attached. The red color cells with "WHI" should remain, and yellow color should be deleted. Is it possible do that?

https://rokastestas.s3.eu-west-3.amazonaws.com/sample2.xlsm

 

[Rick Rothstein]

I just found out a mistake of mine. All of the cells Containing "WHI" has an ending "..WHI-S", "...WHI-M", "...WHI-L" etc. Exept one type of cells containig "WHI" has no additional ending. And I need to keep those cells. Here is another smaple I attached. The red color cells with "WHI" should remain, and yellow color should be deleted. Is it possible do that?

https://rokastestas.s3.eu-west-3.amazonaws.com/sample2.xlsm

Please explain why Rows 18, 34 and 50 (just to name a small few) should be deleted when they contain just WHI at the end.

 

[pisdukas]

Please explain why Rows 18, 34 and 50 (just to name a small few) should be deleted when they contain just WHI at the end.

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