czeknere
04-28-2015, 06:10 AM
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!
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!