Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
IF NOT between
I curently have this code which works wonderfully, i need to adapt it to
delete a line if the value in column DW in NOT equal to or between the values in text boxes 5 and 6 (5 always being less than or equal to 6) Dim rng As Range Dim i As Long Set rng = ActiveSheet.Range(Cells(1, "DW"), Cells(Rows.Count, "DW").End(xlUp)) With rng For i = .Rows.Count To 1 Step -1 If .Cells(i) = UserForm7.TextBox5 Then .Cells(i).EntireRow.Delete End If Next i End With All help very much appreciated Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
IF NOT between
If .Cells(i) < UserForm7.TextBox5 Or .Cells(i) UserForm7.TextBox6 Then
.Cells(i).EntireRow.Delete End If -- Regards, Nigel "Miree" wrote in message ... I curently have this code which works wonderfully, i need to adapt it to delete a line if the value in column DW in NOT equal to or between the values in text boxes 5 and 6 (5 always being less than or equal to 6) Dim rng As Range Dim i As Long Set rng = ActiveSheet.Range(Cells(1, "DW"), Cells(Rows.Count, "DW").End(xlUp)) With rng For i = .Rows.Count To 1 Step -1 If .Cells(i) = UserForm7.TextBox5 Then .Cells(i).EntireRow.Delete End If Next i End With All help very much appreciated Thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
IF NOT between
I have tried this already for some reason it deletes all the rows
"Nigel" wrote: If .Cells(i) < UserForm7.TextBox5 Or .Cells(i) UserForm7.TextBox6 Then .Cells(i).EntireRow.Delete End If -- Regards, Nigel "Miree" wrote in message ... I curently have this code which works wonderfully, i need to adapt it to delete a line if the value in column DW in NOT equal to or between the values in text boxes 5 and 6 (5 always being less than or equal to 6) Dim rng As Range Dim i As Long Set rng = ActiveSheet.Range(Cells(1, "DW"), Cells(Rows.Count, "DW").End(xlUp)) With rng For i = .Rows.Count To 1 Step -1 If .Cells(i) = UserForm7.TextBox5 Then .Cells(i).EntireRow.Delete End If Next i End With All help very much appreciated Thanks |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
IF NOT between
The If..Then statement looks correct to me (given the description of what
you said you wanted to do). Did you actually try the statement or did you just look at it and decide it was the same as what you tried earlier? Are you sure you tried the SAME statement as Nigel posted... with the SAME comparison operators he shows? -- Rick (MVP - Excel) "Miree" wrote in message ... I have tried this already for some reason it deletes all the rows "Nigel" wrote: If .Cells(i) < UserForm7.TextBox5 Or .Cells(i) UserForm7.TextBox6 Then .Cells(i).EntireRow.Delete End If -- Regards, Nigel "Miree" wrote in message ... I curently have this code which works wonderfully, i need to adapt it to delete a line if the value in column DW in NOT equal to or between the values in text boxes 5 and 6 (5 always being less than or equal to 6) Dim rng As Range Dim i As Long Set rng = ActiveSheet.Range(Cells(1, "DW"), Cells(Rows.Count, "DW").End(xlUp)) With rng For i = .Rows.Count To 1 Step -1 If .Cells(i) = UserForm7.TextBox5 Then .Cells(i).EntireRow.Delete End If Next i End With All help very much appreciated Thanks |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
IF NOT between
I was sure this was what I did but tested it anyway and it still did not
work. This is the full code I used maybe the problem is somewhere else Sub FluidVolumeFilter() If UserForm7.TextBox6 = "" Then UserForm7.TextBox6 = UserForm7.TextBox5 End If If UserForm7.TextBox5 = "" Then UserForm7.TextBox5 = UserForm7.TextBox6 End If Dim rng As Range Dim i As Long Set rng = ActiveSheet.Range(Cells(1, "DW"), Cells(Rows.Count, "DW").End(xlUp)) 'Work backwards from bottom to top when deleting rows With rng For i = .Rows.Count To 1 Step -1 If .Cells(i) < UserForm7.TextBox5 Or .Cells(i) UserForm7.TextBox6 Then .Cells(i).EntireRow.Delete End If Next i End With End Sub "Rick Rothstein" wrote: The If..Then statement looks correct to me (given the description of what you said you wanted to do). Did you actually try the statement or did you just look at it and decide it was the same as what you tried earlier? Are you sure you tried the SAME statement as Nigel posted... with the SAME comparison operators he shows? -- Rick (MVP - Excel) "Miree" wrote in message ... I have tried this already for some reason it deletes all the rows "Nigel" wrote: If .Cells(i) < UserForm7.TextBox5 Or .Cells(i) UserForm7.TextBox6 Then .Cells(i).EntireRow.Delete End If -- Regards, Nigel "Miree" wrote in message ... I curently have this code which works wonderfully, i need to adapt it to delete a line if the value in column DW in NOT equal to or between the values in text boxes 5 and 6 (5 always being less than or equal to 6) Dim rng As Range Dim i As Long Set rng = ActiveSheet.Range(Cells(1, "DW"), Cells(Rows.Count, "DW").End(xlUp)) With rng For i = .Rows.Count To 1 Step -1 If .Cells(i) = UserForm7.TextBox5 Then .Cells(i).EntireRow.Delete End If Next i End With All help very much appreciated Thanks |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
IF NOT between
Almost perfect, works most of the time but not if i use a 3 digit and 4 digit
number "Nigel" wrote: Are you testing strings? Because the values in the cells may have unseen space at the end, this is also true for the form controls. I generally remove spaces before any string tests, using the TRIM function. If trim(.Cells(i)) < Trim(UserForm7.TextBox5) Or trim(.Cells(i)) Trim(UserForm7.TextBox6) You might want to assign the test values to variables before the logical test, especially if you intend to use them in later tests, as the code can get a bit messy. For example With UserForm7 sTB5 = Trim(.TextBox5) sTB6 = Trim(.TextBox6) End With For i = .Rows.Count To 1 Step -1 sCell = Trim(.cells(i)) If sCell < sTB5 Or sCell sTB6 then etc. -- Regards, Nigel "Miree" wrote in message ... I was sure this was what I did but tested it anyway and it still did not work. This is the full code I used maybe the problem is somewhere else Sub FluidVolumeFilter() If UserForm7.TextBox6 = "" Then UserForm7.TextBox6 = UserForm7.TextBox5 End If If UserForm7.TextBox5 = "" Then UserForm7.TextBox5 = UserForm7.TextBox6 End If Dim rng As Range Dim i As Long Set rng = ActiveSheet.Range(Cells(1, "DW"), Cells(Rows.Count, "DW").End(xlUp)) 'Work backwards from bottom to top when deleting rows With rng For i = .Rows.Count To 1 Step -1 If .Cells(i) < UserForm7.TextBox5 Or .Cells(i) UserForm7.TextBox6 Then .Cells(i).EntireRow.Delete End If Next i End With End Sub "Rick Rothstein" wrote: The If..Then statement looks correct to me (given the description of what you said you wanted to do). Did you actually try the statement or did you just look at it and decide it was the same as what you tried earlier? Are you sure you tried the SAME statement as Nigel posted... with the SAME comparison operators he shows? -- Rick (MVP - Excel) "Miree" wrote in message ... I have tried this already for some reason it deletes all the rows "Nigel" wrote: If .Cells(i) < UserForm7.TextBox5 Or .Cells(i) UserForm7.TextBox6 Then .Cells(i).EntireRow.Delete End If -- Regards, Nigel "Miree" wrote in message ... I curently have this code which works wonderfully, i need to adapt it to delete a line if the value in column DW in NOT equal to or between the values in text boxes 5 and 6 (5 always being less than or equal to 6) Dim rng As Range Dim i As Long Set rng = ActiveSheet.Range(Cells(1, "DW"), Cells(Rows.Count, "DW").End(xlUp)) With rng For i = .Rows.Count To 1 Step -1 If .Cells(i) = UserForm7.TextBox5 Then .Cells(i).EntireRow.Delete End If Next i End With All help very much appreciated Thanks |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
IF NOT between
You might need to use a type conversion to ensure the test is applied to
numerical values e.g. dblVar = cDbl(yourValue) -- Regards, Nigel "Miree" wrote in message ... Almost perfect, works most of the time but not if i use a 3 digit and 4 digit number "Nigel" wrote: Are you testing strings? Because the values in the cells may have unseen space at the end, this is also true for the form controls. I generally remove spaces before any string tests, using the TRIM function. If trim(.Cells(i)) < Trim(UserForm7.TextBox5) Or trim(.Cells(i)) Trim(UserForm7.TextBox6) You might want to assign the test values to variables before the logical test, especially if you intend to use them in later tests, as the code can get a bit messy. For example With UserForm7 sTB5 = Trim(.TextBox5) sTB6 = Trim(.TextBox6) End With For i = .Rows.Count To 1 Step -1 sCell = Trim(.cells(i)) If sCell < sTB5 Or sCell sTB6 then etc. -- Regards, Nigel "Miree" wrote in message ... I was sure this was what I did but tested it anyway and it still did not work. This is the full code I used maybe the problem is somewhere else Sub FluidVolumeFilter() If UserForm7.TextBox6 = "" Then UserForm7.TextBox6 = UserForm7.TextBox5 End If If UserForm7.TextBox5 = "" Then UserForm7.TextBox5 = UserForm7.TextBox6 End If Dim rng As Range Dim i As Long Set rng = ActiveSheet.Range(Cells(1, "DW"), Cells(Rows.Count, "DW").End(xlUp)) 'Work backwards from bottom to top when deleting rows With rng For i = .Rows.Count To 1 Step -1 If .Cells(i) < UserForm7.TextBox5 Or .Cells(i) UserForm7.TextBox6 Then .Cells(i).EntireRow.Delete End If Next i End With End Sub "Rick Rothstein" wrote: The If..Then statement looks correct to me (given the description of what you said you wanted to do). Did you actually try the statement or did you just look at it and decide it was the same as what you tried earlier? Are you sure you tried the SAME statement as Nigel posted... with the SAME comparison operators he shows? -- Rick (MVP - Excel) "Miree" wrote in message ... I have tried this already for some reason it deletes all the rows "Nigel" wrote: If .Cells(i) < UserForm7.TextBox5 Or .Cells(i) UserForm7.TextBox6 Then .Cells(i).EntireRow.Delete End If -- Regards, Nigel "Miree" wrote in message ... I curently have this code which works wonderfully, i need to adapt it to delete a line if the value in column DW in NOT equal to or between the values in text boxes 5 and 6 (5 always being less than or equal to 6) Dim rng As Range Dim i As Long Set rng = ActiveSheet.Range(Cells(1, "DW"), Cells(Rows.Count, "DW").End(xlUp)) With rng For i = .Rows.Count To 1 Step -1 If .Cells(i) = UserForm7.TextBox5 Then .Cells(i).EntireRow.Delete End If Next i End With All help very much appreciated Thanks |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
IF NOT between
I tried this, but didnt work can you tell me what i am doing wrong, all of
your help is super appreciated. If UserForm7.TextBox6 = "" Then UserForm7.TextBox6 = UserForm7.TextBox5 End If If UserForm7.TextBox5 = "" Then UserForm7.TextBox5 = UserForm7.TextBox6 End If x = CDbl(Trim(UserForm7.TextBox5)) y = CDbl(Trim(UserForm7.TextBox6)) Dim rng As Range Dim i As Long Set rng = ActiveSheet.Range(Cells(1, "DW"), Cells(Rows.Count, "DW").End(xlUp)) 'Work backwards from bottom to top when deleting rows With rng For i = .Rows.Count To 1 Step -1 If Trim(.Cells(i)) < x Or Trim(.Cells(i)) y Then .Cells(i).EntireRow.Delete End If Next i End With "Nigel" wrote: You might need to use a type conversion to ensure the test is applied to numerical values e.g. dblVar = cDbl(yourValue) -- Regards, Nigel "Miree" wrote in message ... Almost perfect, works most of the time but not if i use a 3 digit and 4 digit number "Nigel" wrote: Are you testing strings? Because the values in the cells may have unseen space at the end, this is also true for the form controls. I generally remove spaces before any string tests, using the TRIM function. If trim(.Cells(i)) < Trim(UserForm7.TextBox5) Or trim(.Cells(i)) Trim(UserForm7.TextBox6) You might want to assign the test values to variables before the logical test, especially if you intend to use them in later tests, as the code can get a bit messy. For example With UserForm7 sTB5 = Trim(.TextBox5) sTB6 = Trim(.TextBox6) End With For i = .Rows.Count To 1 Step -1 sCell = Trim(.cells(i)) If sCell < sTB5 Or sCell sTB6 then etc. -- Regards, Nigel "Miree" wrote in message ... I was sure this was what I did but tested it anyway and it still did not work. This is the full code I used maybe the problem is somewhere else Sub FluidVolumeFilter() If UserForm7.TextBox6 = "" Then UserForm7.TextBox6 = UserForm7.TextBox5 End If If UserForm7.TextBox5 = "" Then UserForm7.TextBox5 = UserForm7.TextBox6 End If Dim rng As Range Dim i As Long Set rng = ActiveSheet.Range(Cells(1, "DW"), Cells(Rows.Count, "DW").End(xlUp)) 'Work backwards from bottom to top when deleting rows With rng For i = .Rows.Count To 1 Step -1 If .Cells(i) < UserForm7.TextBox5 Or .Cells(i) UserForm7.TextBox6 Then .Cells(i).EntireRow.Delete End If Next i End With End Sub "Rick Rothstein" wrote: The If..Then statement looks correct to me (given the description of what you said you wanted to do). Did you actually try the statement or did you just look at it and decide it was the same as what you tried earlier? Are you sure you tried the SAME statement as Nigel posted... with the SAME comparison operators he shows? -- Rick (MVP - Excel) "Miree" wrote in message ... I have tried this already for some reason it deletes all the rows "Nigel" wrote: If .Cells(i) < UserForm7.TextBox5 Or .Cells(i) UserForm7.TextBox6 Then .Cells(i).EntireRow.Delete End If -- Regards, Nigel "Miree" wrote in message ... I curently have this code which works wonderfully, i need to adapt it to delete a line if the value in column DW in NOT equal to or between the values in text boxes 5 and 6 (5 always being less than or equal to 6) Dim rng As Range Dim i As Long Set rng = ActiveSheet.Range(Cells(1, "DW"), Cells(Rows.Count, "DW").End(xlUp)) With rng For i = .Rows.Count To 1 Step -1 If .Cells(i) = UserForm7.TextBox5 Then .Cells(i).EntireRow.Delete End If Next i End With All help very much appreciated Thanks |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
IF NOT between
I have built your code and form into Excel 2007, and it works fine.
What entries are you making into the UserForm? What are the range of values in column DW -- Regards, Nigel "Miree" wrote in message ... I tried this, but didnt work can you tell me what i am doing wrong, all of your help is super appreciated. If UserForm7.TextBox6 = "" Then UserForm7.TextBox6 = UserForm7.TextBox5 End If If UserForm7.TextBox5 = "" Then UserForm7.TextBox5 = UserForm7.TextBox6 End If x = CDbl(Trim(UserForm7.TextBox5)) y = CDbl(Trim(UserForm7.TextBox6)) Dim rng As Range Dim i As Long Set rng = ActiveSheet.Range(Cells(1, "DW"), Cells(Rows.Count, "DW").End(xlUp)) 'Work backwards from bottom to top when deleting rows With rng For i = .Rows.Count To 1 Step -1 If Trim(.Cells(i)) < x Or Trim(.Cells(i)) y Then .Cells(i).EntireRow.Delete End If Next i End With "Nigel" wrote: You might need to use a type conversion to ensure the test is applied to numerical values e.g. dblVar = cDbl(yourValue) -- Regards, Nigel "Miree" wrote in message ... Almost perfect, works most of the time but not if i use a 3 digit and 4 digit number "Nigel" wrote: Are you testing strings? Because the values in the cells may have unseen space at the end, this is also true for the form controls. I generally remove spaces before any string tests, using the TRIM function. If trim(.Cells(i)) < Trim(UserForm7.TextBox5) Or trim(.Cells(i)) Trim(UserForm7.TextBox6) You might want to assign the test values to variables before the logical test, especially if you intend to use them in later tests, as the code can get a bit messy. For example With UserForm7 sTB5 = Trim(.TextBox5) sTB6 = Trim(.TextBox6) End With For i = .Rows.Count To 1 Step -1 sCell = Trim(.cells(i)) If sCell < sTB5 Or sCell sTB6 then etc. -- Regards, Nigel "Miree" wrote in message ... I was sure this was what I did but tested it anyway and it still did not work. This is the full code I used maybe the problem is somewhere else Sub FluidVolumeFilter() If UserForm7.TextBox6 = "" Then UserForm7.TextBox6 = UserForm7.TextBox5 End If If UserForm7.TextBox5 = "" Then UserForm7.TextBox5 = UserForm7.TextBox6 End If Dim rng As Range Dim i As Long Set rng = ActiveSheet.Range(Cells(1, "DW"), Cells(Rows.Count, "DW").End(xlUp)) 'Work backwards from bottom to top when deleting rows With rng For i = .Rows.Count To 1 Step -1 If .Cells(i) < UserForm7.TextBox5 Or .Cells(i) UserForm7.TextBox6 Then .Cells(i).EntireRow.Delete End If Next i End With End Sub "Rick Rothstein" wrote: The If..Then statement looks correct to me (given the description of what you said you wanted to do). Did you actually try the statement or did you just look at it and decide it was the same as what you tried earlier? Are you sure you tried the SAME statement as Nigel posted... with the SAME comparison operators he shows? -- Rick (MVP - Excel) "Miree" wrote in message ... I have tried this already for some reason it deletes all the rows "Nigel" wrote: If .Cells(i) < UserForm7.TextBox5 Or .Cells(i) UserForm7.TextBox6 Then .Cells(i).EntireRow.Delete End If -- Regards, Nigel "Miree" wrote in message ... I curently have this code which works wonderfully, i need to adapt it to delete a line if the value in column DW in NOT equal to or between the values in text boxes 5 and 6 (5 always being less than or equal to 6) Dim rng As Range Dim i As Long Set rng = ActiveSheet.Range(Cells(1, "DW"), Cells(Rows.Count, "DW").End(xlUp)) With rng For i = .Rows.Count To 1 Step -1 If .Cells(i) = UserForm7.TextBox5 Then .Cells(i).EntireRow.Delete End If Next i End With All help very much appreciated Thanks |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
IF NOT between
the values range from 100 to 5000, it works if i use any two 3digit numbers
or any two 4digit numbers but when i use a 3digit and 4digit number (was trying 700 and 1400)it deletes all of the rows "Nigel" wrote: I have built your code and form into Excel 2007, and it works fine. What entries are you making into the UserForm? What are the range of values in column DW -- Regards, Nigel "Miree" wrote in message ... I tried this, but didnt work can you tell me what i am doing wrong, all of your help is super appreciated. If UserForm7.TextBox6 = "" Then UserForm7.TextBox6 = UserForm7.TextBox5 End If If UserForm7.TextBox5 = "" Then UserForm7.TextBox5 = UserForm7.TextBox6 End If x = CDbl(Trim(UserForm7.TextBox5)) y = CDbl(Trim(UserForm7.TextBox6)) Dim rng As Range Dim i As Long Set rng = ActiveSheet.Range(Cells(1, "DW"), Cells(Rows.Count, "DW").End(xlUp)) 'Work backwards from bottom to top when deleting rows With rng For i = .Rows.Count To 1 Step -1 If Trim(.Cells(i)) < x Or Trim(.Cells(i)) y Then .Cells(i).EntireRow.Delete End If Next i End With "Nigel" wrote: You might need to use a type conversion to ensure the test is applied to numerical values e.g. dblVar = cDbl(yourValue) -- Regards, Nigel "Miree" wrote in message ... Almost perfect, works most of the time but not if i use a 3 digit and 4 digit number "Nigel" wrote: Are you testing strings? Because the values in the cells may have unseen space at the end, this is also true for the form controls. I generally remove spaces before any string tests, using the TRIM function. If trim(.Cells(i)) < Trim(UserForm7.TextBox5) Or trim(.Cells(i)) Trim(UserForm7.TextBox6) You might want to assign the test values to variables before the logical test, especially if you intend to use them in later tests, as the code can get a bit messy. For example With UserForm7 sTB5 = Trim(.TextBox5) sTB6 = Trim(.TextBox6) End With For i = .Rows.Count To 1 Step -1 sCell = Trim(.cells(i)) If sCell < sTB5 Or sCell sTB6 then etc. -- Regards, Nigel "Miree" wrote in message ... I was sure this was what I did but tested it anyway and it still did not work. This is the full code I used maybe the problem is somewhere else Sub FluidVolumeFilter() If UserForm7.TextBox6 = "" Then UserForm7.TextBox6 = UserForm7.TextBox5 End If If UserForm7.TextBox5 = "" Then UserForm7.TextBox5 = UserForm7.TextBox6 End If Dim rng As Range Dim i As Long Set rng = ActiveSheet.Range(Cells(1, "DW"), Cells(Rows.Count, "DW").End(xlUp)) 'Work backwards from bottom to top when deleting rows With rng For i = .Rows.Count To 1 Step -1 If .Cells(i) < UserForm7.TextBox5 Or .Cells(i) UserForm7.TextBox6 Then .Cells(i).EntireRow.Delete End If Next i End With End Sub "Rick Rothstein" wrote: The If..Then statement looks correct to me (given the description of what you said you wanted to do). Did you actually try the statement or did you just look at it and decide it was the same as what you tried earlier? Are you sure you tried the SAME statement as Nigel posted... with the SAME comparison operators he shows? -- Rick (MVP - Excel) "Miree" wrote in message ... I have tried this already for some reason it deletes all the rows "Nigel" wrote: If .Cells(i) < UserForm7.TextBox5 Or .Cells(i) UserForm7.TextBox6 Then .Cells(i).EntireRow.Delete End If -- Regards, Nigel "Miree" wrote in message ... I curently have this code which works wonderfully, i need to adapt it to delete a line if the value in column DW in NOT equal to or between the values in text boxes 5 and 6 (5 always being less than or equal to 6) Dim rng As Range Dim i As Long Set rng = ActiveSheet.Range(Cells(1, "DW"), Cells(Rows.Count, "DW").End(xlUp)) With rng For i = .Rows.Count To 1 Step -1 If .Cells(i) = UserForm7.TextBox5 Then .Cells(i).EntireRow.Delete End If Next i End With All help very much appreciated Thanks |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
IF NOT between
I just checked I am using 2203, could this be causing a problem?
"Miree" wrote: the values range from 100 to 5000, it works if i use any two 3digit numbers or any two 4digit numbers but when i use a 3digit and 4digit number (was trying 700 and 1400)it deletes all of the rows "Nigel" wrote: I have built your code and form into Excel 2007, and it works fine. What entries are you making into the UserForm? What are the range of values in column DW -- Regards, Nigel "Miree" wrote in message ... I tried this, but didnt work can you tell me what i am doing wrong, all of your help is super appreciated. If UserForm7.TextBox6 = "" Then UserForm7.TextBox6 = UserForm7.TextBox5 End If If UserForm7.TextBox5 = "" Then UserForm7.TextBox5 = UserForm7.TextBox6 End If x = CDbl(Trim(UserForm7.TextBox5)) y = CDbl(Trim(UserForm7.TextBox6)) Dim rng As Range Dim i As Long Set rng = ActiveSheet.Range(Cells(1, "DW"), Cells(Rows.Count, "DW").End(xlUp)) 'Work backwards from bottom to top when deleting rows With rng For i = .Rows.Count To 1 Step -1 If Trim(.Cells(i)) < x Or Trim(.Cells(i)) y Then .Cells(i).EntireRow.Delete End If Next i End With "Nigel" wrote: You might need to use a type conversion to ensure the test is applied to numerical values e.g. dblVar = cDbl(yourValue) -- Regards, Nigel "Miree" wrote in message ... Almost perfect, works most of the time but not if i use a 3 digit and 4 digit number "Nigel" wrote: Are you testing strings? Because the values in the cells may have unseen space at the end, this is also true for the form controls. I generally remove spaces before any string tests, using the TRIM function. If trim(.Cells(i)) < Trim(UserForm7.TextBox5) Or trim(.Cells(i)) Trim(UserForm7.TextBox6) You might want to assign the test values to variables before the logical test, especially if you intend to use them in later tests, as the code can get a bit messy. For example With UserForm7 sTB5 = Trim(.TextBox5) sTB6 = Trim(.TextBox6) End With For i = .Rows.Count To 1 Step -1 sCell = Trim(.cells(i)) If sCell < sTB5 Or sCell sTB6 then etc. -- Regards, Nigel "Miree" wrote in message ... I was sure this was what I did but tested it anyway and it still did not work. This is the full code I used maybe the problem is somewhere else Sub FluidVolumeFilter() If UserForm7.TextBox6 = "" Then UserForm7.TextBox6 = UserForm7.TextBox5 End If If UserForm7.TextBox5 = "" Then UserForm7.TextBox5 = UserForm7.TextBox6 End If Dim rng As Range Dim i As Long Set rng = ActiveSheet.Range(Cells(1, "DW"), Cells(Rows.Count, "DW").End(xlUp)) 'Work backwards from bottom to top when deleting rows With rng For i = .Rows.Count To 1 Step -1 If .Cells(i) < UserForm7.TextBox5 Or .Cells(i) UserForm7.TextBox6 Then .Cells(i).EntireRow.Delete End If Next i End With End Sub "Rick Rothstein" wrote: The If..Then statement looks correct to me (given the description of what you said you wanted to do). Did you actually try the statement or did you just look at it and decide it was the same as what you tried earlier? Are you sure you tried the SAME statement as Nigel posted... with the SAME comparison operators he shows? -- Rick (MVP - Excel) "Miree" wrote in message ... I have tried this already for some reason it deletes all the rows "Nigel" wrote: If .Cells(i) < UserForm7.TextBox5 Or .Cells(i) UserForm7.TextBox6 Then .Cells(i).EntireRow.Delete End If -- Regards, Nigel "Miree" wrote in message ... I curently have this code which works wonderfully, i need to adapt it to delete a line if the value in column DW in NOT equal to or between the values in text boxes 5 and 6 (5 always being less than or equal to 6) Dim rng As Range Dim i As Long Set rng = ActiveSheet.Range(Cells(1, "DW"), Cells(Rows.Count, "DW").End(xlUp)) With rng For i = .Rows.Count To 1 Step -1 If .Cells(i) = UserForm7.TextBox5 Then .Cells(i).EntireRow.Delete End If Next i End With All help very much appreciated Thanks |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
IF NOT between
I have just recreated your values from 100 to 2000, put in 700 and 1400 and
only the values that lie within this range were deleted. I am not aware of any differences between 2003 and 2007 in this respect, but you never know. I cannot try this as I do not have this version on my machine. Maybe someone else can try for you. -- Regards, Nigel "Miree" wrote in message ... I just checked I am using 2203, could this be causing a problem? "Miree" wrote: the values range from 100 to 5000, it works if i use any two 3digit numbers or any two 4digit numbers but when i use a 3digit and 4digit number (was trying 700 and 1400)it deletes all of the rows "Nigel" wrote: I have built your code and form into Excel 2007, and it works fine. What entries are you making into the UserForm? What are the range of values in column DW -- Regards, Nigel "Miree" wrote in message ... I tried this, but didnt work can you tell me what i am doing wrong, all of your help is super appreciated. If UserForm7.TextBox6 = "" Then UserForm7.TextBox6 = UserForm7.TextBox5 End If If UserForm7.TextBox5 = "" Then UserForm7.TextBox5 = UserForm7.TextBox6 End If x = CDbl(Trim(UserForm7.TextBox5)) y = CDbl(Trim(UserForm7.TextBox6)) Dim rng As Range Dim i As Long Set rng = ActiveSheet.Range(Cells(1, "DW"), Cells(Rows.Count, "DW").End(xlUp)) 'Work backwards from bottom to top when deleting rows With rng For i = .Rows.Count To 1 Step -1 If Trim(.Cells(i)) < x Or Trim(.Cells(i)) y Then .Cells(i).EntireRow.Delete End If Next i End With "Nigel" wrote: You might need to use a type conversion to ensure the test is applied to numerical values e.g. dblVar = cDbl(yourValue) -- Regards, Nigel "Miree" wrote in message ... Almost perfect, works most of the time but not if i use a 3 digit and 4 digit number "Nigel" wrote: Are you testing strings? Because the values in the cells may have unseen space at the end, this is also true for the form controls. I generally remove spaces before any string tests, using the TRIM function. If trim(.Cells(i)) < Trim(UserForm7.TextBox5) Or trim(.Cells(i)) Trim(UserForm7.TextBox6) You might want to assign the test values to variables before the logical test, especially if you intend to use them in later tests, as the code can get a bit messy. For example With UserForm7 sTB5 = Trim(.TextBox5) sTB6 = Trim(.TextBox6) End With For i = .Rows.Count To 1 Step -1 sCell = Trim(.cells(i)) If sCell < sTB5 Or sCell sTB6 then etc. -- Regards, Nigel "Miree" wrote in message ... I was sure this was what I did but tested it anyway and it still did not work. This is the full code I used maybe the problem is somewhere else Sub FluidVolumeFilter() If UserForm7.TextBox6 = "" Then UserForm7.TextBox6 = UserForm7.TextBox5 End If If UserForm7.TextBox5 = "" Then UserForm7.TextBox5 = UserForm7.TextBox6 End If Dim rng As Range Dim i As Long Set rng = ActiveSheet.Range(Cells(1, "DW"), Cells(Rows.Count, "DW").End(xlUp)) 'Work backwards from bottom to top when deleting rows With rng For i = .Rows.Count To 1 Step -1 If .Cells(i) < UserForm7.TextBox5 Or .Cells(i) UserForm7.TextBox6 Then .Cells(i).EntireRow.Delete End If Next i End With End Sub "Rick Rothstein" wrote: The If..Then statement looks correct to me (given the description of what you said you wanted to do). Did you actually try the statement or did you just look at it and decide it was the same as what you tried earlier? Are you sure you tried the SAME statement as Nigel posted... with the SAME comparison operators he shows? -- Rick (MVP - Excel) "Miree" wrote in message ... I have tried this already for some reason it deletes all the rows "Nigel" wrote: If .Cells(i) < UserForm7.TextBox5 Or .Cells(i) UserForm7.TextBox6 Then .Cells(i).EntireRow.Delete End If -- Regards, Nigel "Miree" wrote in message ... I curently have this code which works wonderfully, i need to adapt it to delete a line if the value in column DW in NOT equal to or between the values in text boxes 5 and 6 (5 always being less than or equal to 6) Dim rng As Range Dim i As Long Set rng = ActiveSheet.Range(Cells(1, "DW"), Cells(Rows.Count, "DW").End(xlUp)) With rng For i = .Rows.Count To 1 Step -1 If .Cells(i) = UserForm7.TextBox5 Then .Cells(i).EntireRow.Delete End If Next i End With All help very much appreciated Thanks |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
IF NOT between
I will have another look at this problem is maybe else where in the code,
thank you so much for all of your help "Nigel" wrote: I have just recreated your values from 100 to 2000, put in 700 and 1400 and only the values that lie within this range were deleted. I am not aware of any differences between 2003 and 2007 in this respect, but you never know. I cannot try this as I do not have this version on my machine. Maybe someone else can try for you. -- Regards, Nigel "Miree" wrote in message ... I just checked I am using 2203, could this be causing a problem? "Miree" wrote: the values range from 100 to 5000, it works if i use any two 3digit numbers or any two 4digit numbers but when i use a 3digit and 4digit number (was trying 700 and 1400)it deletes all of the rows "Nigel" wrote: I have built your code and form into Excel 2007, and it works fine. What entries are you making into the UserForm? What are the range of values in column DW -- Regards, Nigel "Miree" wrote in message ... I tried this, but didnt work can you tell me what i am doing wrong, all of your help is super appreciated. If UserForm7.TextBox6 = "" Then UserForm7.TextBox6 = UserForm7.TextBox5 End If If UserForm7.TextBox5 = "" Then UserForm7.TextBox5 = UserForm7.TextBox6 End If x = CDbl(Trim(UserForm7.TextBox5)) y = CDbl(Trim(UserForm7.TextBox6)) Dim rng As Range Dim i As Long Set rng = ActiveSheet.Range(Cells(1, "DW"), Cells(Rows.Count, "DW").End(xlUp)) 'Work backwards from bottom to top when deleting rows With rng For i = .Rows.Count To 1 Step -1 If Trim(.Cells(i)) < x Or Trim(.Cells(i)) y Then .Cells(i).EntireRow.Delete End If Next i End With "Nigel" wrote: You might need to use a type conversion to ensure the test is applied to numerical values e.g. dblVar = cDbl(yourValue) -- Regards, Nigel "Miree" wrote in message ... Almost perfect, works most of the time but not if i use a 3 digit and 4 digit number "Nigel" wrote: Are you testing strings? Because the values in the cells may have unseen space at the end, this is also true for the form controls. I generally remove spaces before any string tests, using the TRIM function. If trim(.Cells(i)) < Trim(UserForm7.TextBox5) Or trim(.Cells(i)) Trim(UserForm7.TextBox6) You might want to assign the test values to variables before the logical test, especially if you intend to use them in later tests, as the code can get a bit messy. For example With UserForm7 sTB5 = Trim(.TextBox5) sTB6 = Trim(.TextBox6) End With For i = .Rows.Count To 1 Step -1 sCell = Trim(.cells(i)) If sCell < sTB5 Or sCell sTB6 then etc. -- Regards, Nigel "Miree" wrote in message ... I was sure this was what I did but tested it anyway and it still did not work. This is the full code I used maybe the problem is somewhere else Sub FluidVolumeFilter() If UserForm7.TextBox6 = "" Then UserForm7.TextBox6 = UserForm7.TextBox5 End If If UserForm7.TextBox5 = "" Then UserForm7.TextBox5 = UserForm7.TextBox6 End If Dim rng As Range Dim i As Long Set rng = ActiveSheet.Range(Cells(1, "DW"), Cells(Rows.Count, "DW").End(xlUp)) 'Work backwards from bottom to top when deleting rows With rng For i = .Rows.Count To 1 Step -1 If .Cells(i) < UserForm7.TextBox5 Or .Cells(i) UserForm7.TextBox6 Then .Cells(i).EntireRow.Delete End If Next i End With End Sub "Rick Rothstein" wrote: The If..Then statement looks correct to me (given the description of what you said you wanted to do). Did you actually try the statement or did you just look at it and decide it was the same as what you tried earlier? Are you sure you tried the SAME statement as Nigel posted... with the SAME comparison operators he shows? -- Rick (MVP - Excel) "Miree" wrote in message ... I have tried this already for some reason it deletes all the rows "Nigel" wrote: If .Cells(i) < UserForm7.TextBox5 Or .Cells(i) UserForm7.TextBox6 Then .Cells(i).EntireRow.Delete End If -- Regards, Nigel "Miree" wrote in message ... I curently have this code which works wonderfully, i need to adapt it to delete a line if the value in column DW in NOT equal to or between the values in text boxes 5 and 6 (5 always being less than or equal to 6) Dim rng As Range Dim i As Long Set rng = ActiveSheet.Range(Cells(1, "DW"), Cells(Rows.Count, "DW").End(xlUp)) With rng For i = .Rows.Count To 1 Step -1 If .Cells(i) = UserForm7.TextBox5 Then .Cells(i).EntireRow.Delete End If Next i End With All help very much appreciated Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|