Condensing multiple codes when only changing 1 variable.

[jrake40]

I would like to condense all these vba's since they are doing the same thing with just a different variable. I know this is probably easy I'm just not very familiar with VBA yet.

---

Sub ClearUnwantedCells()


Dim rng As Range
Dim cell As Range
Dim ContainWord As String


Set rng = Range("A1:Z500")


ContainWord = "@"


For Each cell In rng.Cells
If Not cell.Find(ContainWord) Is Nothing Then cell.Clear
Next cell




End Sub

---

Sub ClearUnwantedCells1()


Dim rng As Range
Dim cell As Range
Dim ContainWord As String


Set rng = Range("A1:Z500")


ContainWord = "http"


For Each cell In rng.Cells
If Not cell.Find(ContainWord) Is Nothing Then cell.Clear
Next cell






End Sub

---

Sub ClearUnwantedCells2()


Dim rng As Range
Dim cell As Range
Dim ContainWord As String


Set rng = Range("A1:A100000")


ContainWord = ":"


For Each cell In rng.Cells
If Not cell.Find(ContainWord) Is Nothing Then cell.Clear
Next cell


End Sub

---

Sub ClearUnwantedCells3()


Dim rng As Range
Dim cell As Range
Dim ContainWord As String


Set rng = Range("A1:A100000")


ContainWord = "="


For Each cell In rng.Cells
If Not cell.Find(ContainWord) Is Nothing Then cell.Clear
Next cell


End Sub

 

[lrobbo314]

Something like this?

Sub ClearAll()
Application.ScreenUpdating = False
Dim rng As Range
Dim cell As Range
Dim ContainWord(1 To 4) As String
ContainWord(1) = "@"
ContainWord(2) = "http"
ContainWord(3) = ":"
ContainWord(4) = "="
Set rng = Range("A1:A100000")
For Each cell In rng
    For i = 1 To 4
        If InStr(cell.Value, ContainWord(i)) > 0 Then
            cell.Value = vbNullString
        End If
    Next i
Next cell
Application.ScreenUpdating = True
End Sub

 

[jrake40]

That appears to be what I want. I am getting a mismatch error at this line though.

--If InStr(cell.Value, ContainWord(i)) > 0 Then--

Something like this?

Sub ClearAll()
Application.ScreenUpdating = False
Dim rng As Range
Dim cell As Range
Dim ContainWord(1 To 4) As String
ContainWord(1) = "@"
ContainWord(2) = "http"
ContainWord(3) = ":"
ContainWord(4) = "="
Set rng = Range("A1:A100000")
For Each cell In rng
    For i = 1 To 4
        If InStr(cell.Value, ContainWord(i)) > 0 Then
            cell.Value = vbNullString
        End If
    Next i
Next cell
Application.ScreenUpdating = True
End Sub

 

[lrobbo314]

That's weird. I tested it on some sample data and it worked.

When you get the error, what is in the cell that it is getting stuck on?

 

[jrake40]

It is getting stuck on a formula cell

'=-MMC Alert.'

This is just some data that gets pulled from my source that I want to remove with my vba. It is not actually a formula it just gets pulled over that way for some reason.

 

[lrobbo314]

On your actual spreadsheet, where the "=-MMC Alert" is. Does it display "=-MMC Alert" or does it say "#Name"?

 

[jrake40]

It says '#NAME'. Do i need to change the contain word. I just wasn't sure because it worked previously using = to clear the row.

 

[lrobbo314]

This should do it.

Sub ClearAll()
Application.ScreenUpdating = False
On Error GoTo erTrap
Dim rng As Range
Dim cell As Range
Dim ContainWord(1 To 4) As String
ContainWord(1) = "@"
ContainWord(2) = "http"
ContainWord(3) = ":"
ContainWord(4) = "="
Set rng = Range("A1:A100000")
For Each cell In rng
    For i = 1 To 4
        If InStr(cell.Value, ContainWord(i)) > 0 Then
            cell.Value = vbNullString
        End If
    Next i
Next cell
Application.ScreenUpdating = True
Exit Sub
erTrap:
If Err.Number = 2029 Then
    cell.Value = vbNullString
Else
    MsgBox "Error #" & Err.Number & vbCrLf & Err.Description
End If
    
Application.ScreenUpdating = True
End Sub

 

[jrake40]

I'm confused.. this just displays an error message box. I need the macro to clear that "#Name" row from my spreadsheet along with anything containing the other contain words.

This should do it.

Sub ClearAll()
Application.ScreenUpdating = False
On Error GoTo erTrap
Dim rng As Range
Dim cell As Range
Dim ContainWord(1 To 4) As String
ContainWord(1) = "@"
ContainWord(2) = "http"
ContainWord(3) = ":"
ContainWord(4) = "="
Set rng = Range("A1:A100000")
For Each cell In rng
    For i = 1 To 4
        If InStr(cell.Value, ContainWord(i)) > 0 Then
            cell.Value = vbNullString
        End If
    Next i
Next cell
Application.ScreenUpdating = True
Exit Sub
erTrap:
If Err.Number = 2029 Then
    cell.Value = vbNullString
Else
    MsgBox "Error #" & Err.Number & vbCrLf & Err.Description
End If
    
Application.ScreenUpdating = True
End Sub