works with numbers but not text

[Miree]

I am using the following code, it works fine with numbers but not with text,
can anyone help please
Sub MixerTypeFilter()

If Not UserForm7.ComboBox4.Text = "" Then
Dim MyRange, MyRange1 As Range
Lastrow = Cells(Rows.Count, "DL").End(xlUp).Row
Set MyRange = Sheets("FormulationsDatabase (2)").Range("DL1L" & Lastrow)
For Each c In MyRange
If Not UCase(c.Value) <> UserForm7.ComboBox4.Text Then
If MyRange1 Is Nothing Then
Set MyRange1 = c.EntireRow
Else
Set MyRange1 = Union(MyRange1, c.EntireRow)
End If
End If
Next
If Not MyRange1 Is Nothing Then
MyRange1.Delete
End If
End If

End Sub

 

[Joel]

Did yo try my change? Why are you using double negatives?

from
If Not UCase(c.Value) <> UserForm7.ComboBox4.Text Then


to
If UCase(c.Value) = UCase(UserForm7.ComboBox4.Text) Then

 

[JLGWhiz]

im statement should change From:

Dim MyRange, MyRange1 As Range

To:

Dim MyRange As Range, MyRange1 As Range

Otherwise MyRange is a variant.

This:

If Not UCase(c.Value) <> UserForm7.ComboBox4.Text Then

Equates to this:

If UCase(c.Value) = UserForm7.ComboBox4.Text Then

Maybe you want:

If Not UCase(c.Value) = UserForm7.ComboBox4.Text Then

You might have problems with this:

Set MyRange1 = Union(MyRange1, c.EntireRow)

I didn't test it, but since it would excede the row size
limits, it could cause a problem.

Outside those things, it looks OK.

 

[Dave Peterson]

One more:

Sub MixerTypeFilter()

Dim MyRange as range
dim MyRange1 As Range
dim c as range
dim LastRow as long

with sheets("FormulationsDatabase (2)")
lastrow = .Cells(.Rows.Count, "DL").End(xlUp).Row
set myrange = .range("DL1L" & lastrow
end with

If UserForm7.ComboBox4.Text <> "" Then
set myrange1 = nothing
For Each c In MyRange.cells
If UCase(c.Value) = ucase(UserForm7.ComboBox4.Text) Then
If MyRange1 Is Nothing Then
Set MyRange1 = c
Else
Set MyRange1 = Union(MyRange1, c)
End If
End If
Next c
If MyRange1 Is Nothing Then
'nothing found to delete
else
MyRange1.entirerow.Delete
End If
End If

End Sub

 

[Dave Peterson]

I missed a closing paren:

set myrange = .range("DL1L" & lastrow
should be:
set myrange = .range("DL1L" & lastrow)