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

 

[francopiva]

francopiva,

Here is a macro solution for you to consider.

...

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.

Yeah! It works fine. Thank you hiker95!!!

 

[hiker95]

francopiva,

Thanks for the feedback.

You are very welcome. Glad I could help.

And, come back anytime.

 

[Jungle_V_I_P]

Hello all

I'm new to this forum and very much a beginner with any type of coding but i'm trying to produce workbook for an internal client who needs to transform some report data into a loader.

I'm trying to delete rows where columns x and y have a value. x and y are date values and identify whether someone has left a property or is deceased, these tenants need to be removed to ensure that no correspondence is sent to them.

I'm using the following code (which i have adapted from code I've found in this thread) however am finding that it is deleting an additional row, for someone who does not have a date value. It seems to be happening mid way through the data and I have no idea why.

Can someone please help me with this as it's driving me bananas!

Sub Delete()
'
' Delete Macro
'

'

With ActiveSheet
.AutoFilterMode = False
With Range("x2", Range("x" & Rows.Count).End(xlUp))
.AutoFilter 1, ">0"
On Error Resume Next
.Offset(1).SpecialCells(12).EntireRow.Delete
End With
.AutoFilterMode = False

End With

With ActiveSheet
.AutoFilterMode = False
With Range("y2", Range("y" & Rows.Count).End(xlUp))
.AutoFilter 1, ">0"
On Error Resume Next
.Offset(1).SpecialCells(12).EntireRow.Delete
End With
.AutoFilterMode = False

End With

Range("A1").Select
End Sub


Thanks

 

[jimgun]

francopiva,

Thanks for the feedback.

You are very welcome. Glad I could help.

And, come back anytime.

hello hiker95

i iam confused with this macro

Sub DelIt()
Dim rFnd As Range, dRng As Range, rFst As String, myList, ArrCnt As Long
myList = Array("memento", "database", "gila")

For ArrCnt = LBound(myList) To UBound(myList)
With Range("D1:D" & Range("D" & Rows.Count).End(xlUp).Row)
Set rFnd = .Find(what:=myList(ArrCnt), _
LookIn:=xlValues, _
lookat:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=True)

If Not rFnd Is Nothing Then

rFst = rFnd.Address
Do
If dRng Is Nothing Then
Set dRng = Range("A" & rFnd.Row)
Else
Set dRng = Union(dRng, Range("A" & rFnd.Row))
End If

Set rFnd = .FindNext(After:=rFnd)

Loop Until rFnd.Address = rFst
End If

Set rFnd = Nothing
End With
Next ArrCnt

If Not dRng Is Nothing Then dRng.EntireRow.Delete

End Sub

This macro works only if the value of the cell in coloumn D is exactly "memento" and "database" and "gila"
What if I want to delete all the rows that contains this word "memento" and "database" and "gila" in all column a until z ???

thanks b4

 

[MARK858]

You change...

With Range("D1:D" & Range("D" & Rows.Count).End(xlUp).Row)

to

With Range("A1:Z" & Range("A" & Rows.Count).End(xlUp).Row)

As long as column A is your longest column.

 

[jimgun]

You change...

With Range("D1:D" & Range("D" & Rows.Count).End(xlUp).Row)

to

With Range("A1:Z" & Range("A" & Rows.Count).End(xlUp).Row)

As long as column A is your longest column.

thank you mark858

what if i want to use in all column in the sheet??? not a-z but all??

sorry my english language is very bad......

 

[MARK858]

Change the Z to XFD if you are using 2007 or later but I would seriously consider changing your spreadsheet design or method if you need to do that.

 

[jimgun]

Change the Z to XFD if you are using 2007 or later but I would seriously consider changing your spreadsheet design or method if you need to do that.

what is the meaning changing method or spreadsheet design??

is there a more simple method???

 

[MARK858]

Not more simple but it is a lot of searching and deleting to go through 16000 columns and if you need to do that it is time to redesign the sheet.

I will put it this way if it is all 16000 columns then I have never had to do that.

If what you mean is all the used columns then there are ways to do that but even so if you had to do that on too many columns then there is something wrong with the layout.

 

[hiker95]

This macro works only if the value of the cell in coloumn D is exactly "memento" and "database" and "gila"
What if I want to delete all the rows that contains this word "memento" and "database" and "gila" in all column a until z ???

jimgun,

I prefer to see the actual raw data worksheet(s), and, what the results should look like.


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


If you are not able to provide screenshots, then:

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