0

I try to delete row that does not contain a certain type of word in B column but it can have everything in front and after the word.

Select Case Range("B7:B1048576").Value
    Case "*Transpalette*"
        'do nothing
    Case Else
        Selection.EntireRow.Delete
End Select
Iron Dragon
  • 1
  • 1
  • 1
    You will need to iterate, You cannot test a single value against a range array. It would be quicker to use filter to filter out the ones that contain then delete the visible rows. There are many examples on how to do that on this site alone. – Scott Craner Sep 24 '21 at 15:23
  • In addition to what Scott Craner has mentioned you need modify your case statement to properly handle wildcards since they evaluate to a boolean. https://stackoverflow.com/questions/18409418/excel-vba-select-case-if-activecell-like-string – Warcupine Sep 24 '21 at 15:26
  • How do I mark this solved? – Iron Dragon Sep 24 '21 at 15:34

1 Answers1

0
Sub Test()
Dim i As Long, Lastrow As Long

Lastrow = Cells(Rows.Count, 1).End(xlUp).Row
For i = Lastrow To 2 Step -1
Select Case True
    Case ActiveSheet.Range("B" & i).Value Like "*Transpalette*"
    Case Else
    ActiveSheet.Range("B" & i).EntireRow.Delete
End Select
Next i
End Sub

Hope the above code solve your query

Gary's Student
  • 95,722
  • 10
  • 59
  • 99
Santhosh Kumar
  • 1
  • 1
  • Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Sep 24 '21 at 18:01