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]

SiuGuy007,

1. What version of Excel, and, Windows are you using?

2. Are you using a PC or a Mac?


If I understand you correctly, then here is another macro solution for you to consider, that does not do any looping thru the rows, and, does not use AutoFilter, and, should be very fast.

Sample raw data in the active worksheet:

Excel 2007
	A	W
1	Title A	Title W
2		I am Well
3		I am not Well
4		Alive Well
5		I am Well
6		I am not Well
7		Alive Well
8		I am Well
9		Alive Well
10		I am Well
11		Alive Well
12		I am not Well
13
Sheet1

And, after the macro:

Excel 2007
	A	W
1	Title A	Title W
2		I am Well
3		I am not Well
4		I am Well
5		I am not Well
6		I am Well
7		I am Well
8		I am not Well
9
10
11
12
13
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).

Sub SiuGuy007()
' hiker95, 03/04/2016, ME300330
Columns("W").Replace "Alive Well", "#N/A", xlWhole
On Error Resume Next
Columns("W").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 SiuGuy007 macro.

 

[hiker95]

Thanks again...I am a VBA newb, so please forgive me. I copied the Code and run it. Again, not changes to the spreadsheet. In column W, the text will be Alive/Well Check or Alive/Well Check, Activity Check.
When the code looks for the word does it have to be an exact match or if it find the 2 words, even if there are additional words, coma, slash etc?

SiuGuy007,

It looks like I did not understand you correctly.

Can you supply a list of separate strings that can be in the cells in column W (beginning in W2), and, which one/ones you want to delete the rows for?

 

[SiuGuy007]

Thank you for the reply. I use Windows 7, Excel 2010, PC

 

[SiuGuy007]

Hiker95

Is there a way I could attach the excel sheet to show you?

 

[hiker95]

Hiker95

Is there a way I could attach the excel sheet to show you?

SiuGuy007,

You can not attach the excel sheet, but:

See reply #2 at the next link, if you want to show small screenshots.

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), and provide us with a link to your workbook:

https://dropbox.com

 

[SiuGuy007]

hiker95

Below is the link to view the excel sheet. Many Thanks..

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

 

[hiker95]

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:

Excel 2007
	A	B	C	S	T	U	V	W	X
1	Case ID	Master Case ID	Case Status	Service	Days	Vendor	Vendor Type	OfficeID	Office
2	396640	257222	Case Completed	Alive/Well Check	0.0	NBA	GO	37	FW
3	396721	257263	Case Completed	Alive/Well Check	0.0	NBA	GO	38	SA
4	396721	257263	Case Completed	County Criminal History	0.0	NBA	GO	38	SA
5	396721	257263	Case Completed	County Civil Records	0.0	NBA	GO	38	SA
6
ServRaw

And, after the new macro:

Excel 2007
	A	B	C	S	T	U	V	W	X
1	Case ID	Master Case ID	Case Status	Service	Days	Vendor	Vendor Type	OfficeID	Office
2	396721	257263	Case Completed	County Criminal History	0.0	NBA	GO	38	SA
3	396721	257263	Case Completed	County Civil Records	0.0	NBA	GO	38	SA
4
5
6
ServRaw

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).

Sub SiuGuy007_V2()
' hiker95, 03/04/2016, ME300330
Columns("S").Replace "Alive/Well*", "#N/A", xlWhole
On Error Resume Next
Columns("S").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 SiuGuy007_V2 macro.

 

[SiuGuy007]

Hiker95.....
Works perfectly....Many Thanks!!!

 

[hiker95]

SiuGuy007,

Thanks for the feedback.

You are very welcome. Glad I could help.

And, come back anytime.

 

[SiuGuy007]

If I have other rows that I wanted to delete, I would just change the name in Column Replace line, correct? What is the name is similar to this "ESES (245)"?