works with numbers but not text

  • Thread starter Thread starter Miree
  • Start date Start date
M

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("DL1:DL" & 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
 
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
 
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.
 
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("DL1:DL" & 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
 
I missed a closing paren:

set myrange = .range("DL1:DL" & lastrow
should be:
set myrange = .range("DL1:DL" & lastrow)
 
Back
Top