How to delete a row if it contains a word?

[truzilla]

Hi everyone! Just wanted to say this place has been an amazing help.

So basically I have a spreadsheet with a list in column A, and I would like it to delete any rows that start with/contain the words (for example) "happy" and "angry". I also need the same this for columns, as there are headings across the spreadsheet in row 1. For example, if columns (all in row 1) A,D,G, and T contain the word "data" I would like to delete those columns.

Thank you so much in advance for your help and I will definitely clarify if it doesnt make sense!

[truzilla]

any luck por favor? =/

[Hollbrown]

Not a VBA answer, but quick and dirty you can add a column to the left of the column of cells containing text. The following formula will return a 1 if the cell doesn't contain the text, a 0 if it does. Sort the rows then delete what you don't want.

=IF(ISERROR(FIND("happy",B4)=TRUE),1,0)

[Simon Lloyd]

How about this:
[VBA]
Sub Del_Rows()
Dim MyCell As Range, oCell As Range
MsgBox Range(Cells(1, 1), Cells(1, Columns.Count).End(xlToLeft)).Address
For Each oCell In Range(Cells(1, 1), Cells(1, Columns.Count).End(xlToLeft))
If oCell.Value = "Data" Then oCell.EntireColumn.Delete
Next oCell
For Each MyCell In ActiveSheet.UsedRange
If Not IsError(Application.Match(MyCell.Value, _
Array("Happy", "Angry"), 0)) Then MyCell.EntireRow.Delete
Next MyCell
End Sub
[/VBA]

[truzilla]

hm sorry, that one didn't work..but I did some more searching and found out how to make it work with rows...BUT

Does anyone know how to tweak this code to make it work with columns? (Delete columns that have a specific word in Row 1)..

THANKS! (row delete code below)

[VBA]
Sub Delete_Rows_BS()
Dim rng As Range
Dim calcmode As Long
Dim myArr As Variant
Dim I As Long
With Application
calcmode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With
'Fill in the values that you want to delete
myArr = Array("Subtotal Level 7", "Additional Netting", "*Total*")
For I = LBound(myArr) To UBound(myArr)
'Sheet with the data, you can also use Sheets("MySheet")
With ActiveSheet
'Firstly, remove the AutoFilter
.AutoFilterMode = False
'Apply the filter
.Range("B1:B" & .Rows.Count).AutoFilter Field:=1, Criteria1:=myArr(I)
Set rng = Nothing
With .AutoFilter.Range
On Error Resume Next
Set rng = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not rng Is Nothing Then rng.EntireRow.Delete
End With
'Remove the AutoFilter
.AutoFilterMode = False
End With
Next I
With Application
.ScreenUpdating = True
.Calculation = calcmode
End With
End Sub
[/VBA]

[Bob Phillips]

Which column? the one with the word in it? What if in many columns?

[Simon Lloyd]

hm sorry, that one didn't work..but I did some more searching and found out how to make it work with rows...BUT

Which doesn't work? my example? worked perfect for me, of course if you don't have the exact words as they are shown in the code then it wont work, you cannot have typed spaces before or after, if the word is sometimes in uppercase or sometimes in lowercase then you need to use the If Lcase( statement, but to be honest if you had supplied more information and a sample workbook you would have had a perfect answer already!

[RichardSchollar]

Hi Simon

Yours also wouldn't work properly because you were deleting rows/columns without ensuring you were looping backwards through the range. This would only matter if cells marked for deletion were in adjacent rows/columns.

Richard

[Simon Lloyd]

Richard you're right i was very lazy when constructing the code and i only tested it once!

[Bob Phillips]

Richard you're right i was very lazy when constructing the code and i only tested it once!

... only once! You sometimes test more?

[Simon Lloyd]

... only once! You sometimes test more?

Errr....Bob i have to confess i rarely test code i post, normally if it compiles i post it!

[Simon Lloyd]

Ok, tested twice!!!
[VBA]
Sub Del_Rows()
Dim MyCell As Long
Dim i As Long
For i = Cells(1, Columns.Count).End(xlToLeft).Column To 1 Step -1
If Cells(1, i).Value = "Data" Then Cells(1, i).EntireColumn.Delete
Next i
For MyCell = ActiveSheet.UsedRange.Rows.Count To 2 Step -1
If Application.WorksheetFunction.CountIf(Rows(MyCell), "Happy") >= 1 _
Or Application.WorksheetFunction.CountIf(Rows(MyCell), "Angry") >= 1 _
Then Rows(MyCell).EntireRow.Delete
Next MyCell
End Sub
[/VBA]