Trying to delete cells with specific terms, Runtime 424 Object Required error

[czeknere]

I'm trying to write a macro for a co-worker and I'm learning VBA as I go, so forgive me if any of this is a newbie question:

I've got a sub that searches through a range of cells for specific search terms and if found, it deletes the cell. Code below:


Sub exclusionDelete()

Dim rngCell As Range
Dim IngLastRow As Long
Dim SearchString() As String
Dim IntStrMax As Integer

IntStrMax = 5
ReDim SearchString(1 To IntStrMax)

SearchString(1) = "Term1"
SearchString(2) = "Term2"
SearchString(3) = "Term3"
SearchString(4) = "Term4"
SearchString(5) = "Term5"


lngLstRow = ActiveWorkbook.ActiveSheet.UsedRange.Rows.Count


For Each rngCell In Range("C2:D" & lngLstRow)
For i = 1 To IntStrMax
If SearchString(i) = rngCell.Value Or InStr(rngCell.Value, SearchString(i)) > 0 Then
rngCell.EntireRow.Delete
End If
Next i
Next

End Sub


I've removed the actual search terms since it's company info, but this is the general idea.

I am getting the error on the 'rngCell.EntireRow.Delete' line. Oddly enough, the code works perfectly on my computer, a macbook running Excel 2011, but on my coworker's PC running Excel 2010 it throws an error. However, if I change that line to do something else for example: 'rngCell.EntireRow.Interior.ColorIndex = 3' it works just fine.

I'd really appreciate any help on this!

[mancubus]

i understand if Column C or Column D contain any of the Search strings you want to delete their rows.

try this after backing up the file:




Sub vbax_52430_DeleteRowsOnBasedCondition()

Dim SearchString
Dim i As Long, j As Long, calc As Long

With Application
.DisplayAlerts = False
.ScreenUpdating = False
.EnableEvents = False
calc = .Calculation
.Calculation = xlCalculationManual
End With

SearchString = Array("Term1", "Term2", "Term3", "Term4", "Term5")

With ActiveSheet
.AutoFilterMode = False
For j = 3 To 4 '3 = Col C and 4 = Col D
For i = LBound(SearchString) To UBound(SearchString)
.Cells(1).AutoFilter Field:=j, Criteria1:="=*" & SearchString(i) & "*"
If .AutoFilter.Range.Rows.Count > 1 Then
.UsedRange.Columns(1).Offset(1).SpecialCells(12).EntireRow.Delete
End If
Next i
.AutoFilterMode = False
Next j
End With

With Application
.EnableEvents = False
.Calculation = calc
End With

End Sub

[czeknere]

Thanks for the quick help!

When I run your code I'm getting a Run-time error 1004 - Application-Defined or object-defined error.

[mancubus]

i assumed topleft cell of your table as A1.
is it so?

[czeknere]

Correct. Four columns, a,b,c & d. A1 is the first cell.

[mancubus]

can you post your workbook by replacing the sensitive data with some values.

[czeknere]

Absolutely, it's attached here. This is a shortened version, I can supply the longer version if necessary.

[mancubus]

the code worked with the sample file.

did you replace the elements of Array("Term1", "Term2", "Term3", "Term4", "Term5") with actual ones in the code?

[czeknere]

Just to be on the safe side - here's a longer version with information removed.

Thanks for the help!

[czeknere]

I did, yes. Can you test it on the longer file that I just attached?

[mancubus]

worked with the new file too.
see attached file...

[czeknere]

Interesting.

It may actually have something to do with how I'm implementing the macro then. I was working out of my Personal Macro Workbook since this sub routine is part of a larger collection of things I need to run. When I copied the code you supplied over to a brand new macro it ran just fine. I guess I need to go back and reset the whole collection of sub routines.

Thanks for all of the help! I really appreciate it!

[mancubus]

you are welcome.

please mark the thread as solved for future references.

there can be times when macros dont run as expected. in such cases i create a new workbook move modules here from previous workbook.

i regularly clear all the files in temp folder even though most of them are cleared when shutting down the computer.