works with numbers but not text
 

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

works with numbers but not text
 

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


"Miree" wrote:

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

works with numbers but not text
 

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.




"Miree" wrote in message
...
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

works with numbers but not text
 

One mo

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

Miree wrote:

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

--

Dave Peterson

works with numbers but not text
 

I missed a closing paren:

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

Dave Peterson wrote:

One mo

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

Miree wrote:

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

--

Dave Peterson

--

Dave Peterson