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

 

[hiker95]

I fixed the spreadsheet.

https://www.dropbox.com/s/uhkq6trv88ftc9t/Monthly Report VBA ESIS.xlsx?dl=0

Thanks,

Michael

SiuGuy007,

I can launch your link, but, I can not click on download, and, I can not copy the data?

You can post your workbook/worksheets to the following free site (sensitive data changed), mark the workbook for sharing, and, provide us with a link to your workbook:

https://dropbox.com

 

[excelman888]

Hi

Try the code below

Hope it works for you

Mark

Sub DeleteRowWithContents()
'========================================================================
' DELETES ALL ROWS FROM A2 DOWNWARDS WITH THE WORDs "Record Only" IN COLUMN D
'========================================================================
    Last = Cells(Rows.Count, "D").End(xlUp).Row
    For i = Last To 1 Step -1
        If (Cells(i, "D").Value) = "Record Only" Then
    'Cells(i, "A").EntireRow.ClearContents ' USE THIS TO CLEAR CONTENTS BUT NOT DELETE ROW
            Cells(i, "A").EntireRow.Delete
        End If
    Next i
End Sub

Is it possible to use a tickbox which inserts the data again.

Or if not possible just hide unhide the relevant cells

 

[SiuGuy007]

Is it possible to use a tickbox which inserts the data again.

Or if not possible just hide unhide the relevant cells

I am not sure what a "tickbox" is?

 

[SiuGuy007]

Excelman888

Many thanks for your assistance......

 

[th3g3rmb0y]

Why loop ?
Try AutoFilter ...

Sub test()
With ActiveSheet
    .AutoFilterMode = False
    With Range("d1", Range("d" & Rows.Count).End(xlUp))
        .AutoFilter 1, "*Record Only*"
        On Error Resume Next
        .Offset(1).SpecialCells(12).EntireRow.Delete
    End With
    .AutoFilterMode = False
End With
End Sub

PURE MAGIC - love it

 

[SiuGuy007]

Thank you so much for this. (My apologies for not responding sooner, as I overlooked this email) I just tried this and works great.....Is there a way to add additonal words? For example, in addition to Record Only, add Record Only, Video, Background"

 

[francopiva]

SiuGuy007,

Thanks for the workbook/worksheet.

Here is a new macro for you to consider based on the new raw data structure.

Sample raw data:

...

Sub SiuGuy007_ESIS_Plus()
' hiker95, 03/07/2016, ME300330
Columns("I").Replace "ESIS", "#N/A", xlWhole
Columns("I").Replace "ESIS*", "#N/A", xlWhole
On Error Resume Next
Columns("I").SpecialCells(xlConstants, xlErrors).EntireRow.Delete
On Error GoTo 0
End Sub

Wow, that is great! Thank you.
Is it possible to do the same thing on the rows containing a specific string? I mean: this macro works only if the value of the cell is exactly "ESIS". What about if would like to remove the cells like "TExt 1 ESIS", "Text Text ESIS Text"?

 

[hiker95]

Is it possible to do the same thing on the rows containing a specific string? I mean: this macro works only if the value of the cell is exactly "ESIS". What about if would like to remove the cells like "TExt 1 ESIS", "Text Text ESIS Text"?

francopiva,

Welcome to the MrExcel forum.


So that I can get it right on the first try:

We would like more information. Please see the Forum Use Guidelines in the following link:

http://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html


See reply #2 at the next link, if you want to show small screenshots, of the raw data, and, what the results should look like.

http://www.mrexcel.com/forum/about-board/508133-attachments.html#post2507729


Or, you can post your workbook/worksheets to the following free site (sensitive data changed), mark the workbook for sharing, and, provide us with a link to your workbook:

https://dropbox.com

 

[francopiva]

francopiva,

Welcome to the MrExcel forum.


So that I can get it right on the first try:

We would like more information. Please see the Forum Use Guidelines in the following link:

http://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html


See reply #2 at the next link, if you want to show small screenshots, of the raw data, and, what the results should look like.

http://www.mrexcel.com/forum/about-board/508133-attachments.html#post2507729


Or, you can post your workbook/worksheets to the following free site (sensitive data changed), mark the workbook for sharing, and, provide us with a link to your workbook:

https://dropbox.com

I had a look at the entire thread before posting my question but I haven't found anything. I thought my question does not need any images to be explained: I apologize for my english...

 

[hiker95]

francopiva,

Here is a macro solution for you to consider.

Sample raw data:

Excel 2007
	I
1	Title I
2	ESIS
3	not this
4	not this
5	TExt 1 ESIS
6	not this
7	not this
8	not this
9	Text Text ESIS Text
10	not this
11
Sheet1

And, after the macro:

Excel 2007
	I
1	Title I
2	not this
3	not this
4	not this
5	not this
6	not this
7	not this
8
9
10
11
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Sub francopiva_ESIS_Plus()
' hiker95, 11/30/2016, ME300330
Columns("I").Replace "ESIS", "#N/A", xlWhole
Columns("I").Replace "ESIS*", "#N/A", xlWhole
Columns("I").Replace "*ESIS*", "#N/A", xlWhole
On Error Resume Next
Columns("I").SpecialCells(xlConstants, xlErrors).EntireRow.Delete
On Error GoTo 0
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the francopiva_ESIS_Plus macro.