VBA Delete Entire Row if Contains Certain Text

[bigmacneb]

I've searched on here, but every code I put in gives me an error back. Data in column D, If any of the cells contains "Record Only" I need it to delete the entire row.
Thanks

 

[Martin_King]

Sorry, thought as it was sort of related

Anyway, not sure exactly what you mean.

In column B, there are many cells containing the word Total only. I have ammended the code to "Total" and still nothing.

The forumla summin values are right of C. B is purely Text

 

[mirabeau]

I tried your code with just "Total" in a cell and it seemed to work.

However, modify it to

Sub Test_DeleteRows2()
Dim x As Long
FinalRow = Cells(Rows.Count, 2).End(xlUp).Row
Sheets("Sheet1").Activate
For x = FinalRow To 30 Step -1
    If Cells(x, 2).Value = "Total" Then
        Range(Rows(x + 8), Rows(x - 30)).Select
        Selection.EntireRow.Delete
    End If
Next x
End Sub

Also, there's generally no need to select before deleting.

Although selecting and pausing the code is one way of seeing if you have in fact chosen the correct cells or rows to delete.

 

[Martin_King]

How wierd, I created a new sheet and entered manual text and it worked.

For some reason, it isn't working on the data I am importing into the workbook. I have tried pasting as values and manually entering the word Total over the top and nothing. Difficult to think of a work around, not sure why it isn't running on this source.

 

[Martin_King]

work around = inserted column A, if B = Total then true, delete all rows where A = true

No idea why this method works but seems to be doing the trick

thanks for the help

 

[taurean]

How wierd, I created a new sheet and entered manual text and it worked.

For some reason, it isn't working on the data I am importing into the workbook. I have tried pasting as values and manually entering the word Total over the top and nothing. Difficult to think of a work around, not sure why it isn't running on this source.

Maybe check if the cell contains something more [as suggested by mirabeau] like some non printing characters. Its quite possible if you are importing data from some other software.

 

[BradH]

Maybe check if the cell contains something more [as suggested by mirabeau] like some non printing characters. Its quite possible if you are importing data from some other software.

I am having this issue, is there a wildcard I can use so that the non printing characters are ignored?

I tried Out of State*, but it did not work.

 

[puppy26]

Hi Mark,

I want to write a program that should update the already written macro program.
It is how now..
I have already updated the Master excel sheet with the following information manually.
Calendar Length
BU PORSCHE APPAREL EU-HERZO Inline NEW 17 months
BU REGIONAL SPORTS APPAREL CCT Inline NEW 18 months
etc
.
.
.


In the data sheet, I have 20000 models. I wrote a macro program in such a way if the follwing conditions satisfy, the calendar length will be assigned to corresponding model.

If Cells(i, "D").Value = "BU PORSCHE" And Cells(i, "G").Value = "APPAREL" And Cells(i, "J").Value = "EU-HERZO" And Cells(i, "K").Value = "INLINE" And Cells(i, "L").Value = "NEW" Then
Cells(i, "O").Value = 17
End If

In the future, the calendar length will vary and it's difficult to change the calendar length in the macro program each time.
So I want some solutions about how to update the calendar length in the macro program automatically if I change the calendar length in the Master sheet.

Am i clear now!!

Can you help me in this please..

Hi dude,
Could you please help me with code for the following condition.,.

it should be like if the particular condition satisfies then the entire row should be deleted.,.

Can you please help me..

 

[BradH]

I figured it out, I needed to use Like instead of =.

Thanks for the script!

 

[taurean]

You can also use Instr function like:

If Instr(MainString,StringToFind) > 0 Then
'Code Here
End If

 

[simopie]

Hi all,

It's been a long time since i used VBA and didn't realize just how much I'd forgotten.
I have a large list of data, and I want to be able to look at the string in Column B and see if it contains any of up to 100 key words (e.g. plc, ltd, limited).
If any of these words are contained, then I would like it to place a Y in Column J, and if not, place a N.

I have so far only entered 3 different strings to search to test the macro, but it is placing a N for all cells in column J when some of them should be a Y.

The code I have so far is:


Sub Test_Macro()

Last = Cells(Rows.Count, "B").End(xlUp).Row

For i = 2 To Last
If (Cells(i, "B").Value) = "*ltd*" Or (Cells(i, "B").Value) = "*limited*" Or (Cells(i, "B").Value) = "*plc*" Then
Cells(i, "J").Value = "Y"
Else: Cells(i, "J").Value = "N"
End If
Next i

End Sub



Any ideas where I going wrong?

Thanks in advance