|
Help with code
With Sheet1 containing in Cells A1:A11
a1 a2 a3 a4 b1 b2 b3 b4 c1 c2 c3 Why is this code Not deleting all and only the ?3's and ?4's Right Now it is deleting ALL/everything !! Hair-pulling here ... Sub foo() Set rng = ActiveSheet.Range(Cells(1, 1), Cells(Cells(Rows.Count, 1).End(xlUp).Row, 1)) lrow = rng.Rows.Count For i = lrow To 1 Step -1 t = Right(Cells(i, 1).Value, 1) If t < "1" Or t < "2" Then Cells(i, 1).EntireRow.Delete End If Next End Sub |
Help with code
Whoops, happened to call my Subject Help with code ALSO!!!
"JMay" wrote in message : With Sheet1 containing in Cells A1:A11 a1 a2 a3 a4 b1 b2 b3 b4 c1 c2 c3 Why is this code Not deleting all and only the ?3's and ?4's Right Now it is deleting ALL/everything !! Hair-pulling here ... Sub foo() Set rng = ActiveSheet.Range(Cells(1, 1), Cells(Cells(Rows.Count, 1).End(xlUp).Row, 1)) lrow = rng.Rows.Count For i = lrow To 1 Step -1 t = Right(Cells(i, 1).Value, 1) If t < "1" Or t < "2" Then Cells(i, 1).EntireRow.Delete End If Next End Sub |
Help with code
Number vs text. Try this
Set rng = ActiveSheet.range(Cells(1, 1), Cells(Cells(Rows.Count, 1).End(xlUp).Row, 1)) For i = rng.Rows.Count To 1 Step -1 t = Right(Cells(i, 1), 1) If t < 1 Or t < 2 Then Rows(i).Delete End If Next -- Don Guillett SalesAid Software "JMay" wrote in message ... With Sheet1 containing in Cells A1:A11 a1 a2 a3 a4 b1 b2 b3 b4 c1 c2 c3 Why is this code Not deleting all and only the ?3's and ?4's Right Now it is deleting ALL/everything !! Hair-pulling here ... Sub foo() Set rng = ActiveSheet.Range(Cells(1, 1), Cells(Cells(Rows.Count, 1).End(xlUp).Row, 1)) lrow = rng.Rows.Count For i = lrow To 1 Step -1 t = Right(Cells(i, 1).Value, 1) If t < "1" Or t < "2" Then Cells(i, 1).EntireRow.Delete End If Next End Sub |
Help with code
Don Thanks, But.... This is Strange *&^*&%$
Your suggested code also Deleted All my rows. Your code below WITH ALTERATIONS - noted with comments Sub DGfoo() Set rng = ActiveSheet.Range(Cells(1, 1), Cells(Cells(Rows.Count, 1).End(xlUp).Row, 1)) For i = rng.Rows.Count To 1 Step -1 t = Right(Cells(i, 1), 1) ' t is text If IsNumeric(t) Then MsgBox t & " is text" 'proof that t is text If t < "1" Or t < "2" Then 'enter 1 and 2 as text (using quotes) Rows(i).Delete End If Next End Sub CONFUSED, Can you see why? Jim "Don Guillett" wrote: Number vs text. Try this Set rng = ActiveSheet.range(Cells(1, 1), Cells(Cells(Rows.Count, 1).End(xlUp).Row, 1)) For i = rng.Rows.Count To 1 Step -1 t = Right(Cells(i, 1), 1) If t < 1 Or t < 2 Then Rows(i).Delete End If Next -- Don Guillett SalesAid Software "JMay" wrote in message ... With Sheet1 containing in Cells A1:A11 a1 a2 a3 a4 b1 b2 b3 b4 c1 c2 c3 Why is this code Not deleting all and only the ?3's and ?4's Right Now it is deleting ALL/everything !! Hair-pulling here ... Sub foo() Set rng = ActiveSheet.Range(Cells(1, 1), Cells(Cells(Rows.Count, 1).End(xlUp).Row, 1)) lrow = rng.Rows.Count For i = lrow To 1 Step -1 t = Right(Cells(i, 1).Value, 1) If t < "1" Or t < "2" Then Cells(i, 1).EntireRow.Delete End If Next End Sub |
Help with code
Sorry - Forget My Last Send - This is what I meant to send you
Sub DGfoo() Set rng = ActiveSheet.Range(Cells(1, 1), Cells(Cells(Rows.Count, 1).End(xlUp).Row, 1)) For i = rng.Rows.Count To 1 Step -1 t = Right(Cells(i, 1), 1) ' t is numeric If IsNumeric(t) Then MsgBox t & " is numeric" 'proof that t is numeric If t < 1 Or t < 2 Then 'enter 1 and 2 as numeric Rows(i).Delete End If Next End Sub "Don Guillett" wrote: Number vs text. Try this Set rng = ActiveSheet.range(Cells(1, 1), Cells(Cells(Rows.Count, 1).End(xlUp).Row, 1)) For i = rng.Rows.Count To 1 Step -1 t = Right(Cells(i, 1), 1) If t < 1 Or t < 2 Then Rows(i).Delete End If Next -- Don Guillett SalesAid Software "JMay" wrote in message ... With Sheet1 containing in Cells A1:A11 a1 a2 a3 a4 b1 b2 b3 b4 c1 c2 c3 Why is this code Not deleting all and only the ?3's and ?4's Right Now it is deleting ALL/everything !! Hair-pulling here ... Sub foo() Set rng = ActiveSheet.Range(Cells(1, 1), Cells(Cells(Rows.Count, 1).End(xlUp).Row, 1)) lrow = rng.Rows.Count For i = lrow To 1 Step -1 t = Right(Cells(i, 1).Value, 1) If t < "1" Or t < "2" Then Cells(i, 1).EntireRow.Delete End If Next End Sub |
Help with code
Let's try again,,, Here is what I meant to send you - Scratch the other..
Sub DGfoo() Set rng = ActiveSheet.Range(Cells(1, 1), Cells(Cells(Rows.Count, 1).End(xlUp).Row, 1)) For i = rng.Rows.Count To 1 Step -1 t = Right(Cells(i, 1), 1) ' t is numeric If IsNumeric(t) Then MsgBox t & " is numeric" 'proof that t is numeric If t < 1 Or t < 2 Then 'enter 1 and 2 as numeric Rows(i).Delete End If Next End Sub "Don Guillett" wrote: Number vs text. Try this Set rng = ActiveSheet.range(Cells(1, 1), Cells(Cells(Rows.Count, 1).End(xlUp).Row, 1)) For i = rng.Rows.Count To 1 Step -1 t = Right(Cells(i, 1), 1) If t < 1 Or t < 2 Then Rows(i).Delete End If Next -- Don Guillett SalesAid Software "JMay" wrote in message ... With Sheet1 containing in Cells A1:A11 a1 a2 a3 a4 b1 b2 b3 b4 c1 c2 c3 Why is this code Not deleting all and only the ?3's and ?4's Right Now it is deleting ALL/everything !! Hair-pulling here ... Sub foo() Set rng = ActiveSheet.Range(Cells(1, 1), Cells(Cells(Rows.Count, 1).End(xlUp).Row, 1)) lrow = rng.Rows.Count For i = lrow To 1 Step -1 t = Right(Cells(i, 1).Value, 1) If t < "1" Or t < "2" Then Cells(i, 1).EntireRow.Delete End If Next End Sub |
Help with code
try this:
Sub DGfoo() Dim i As Long, t As Integer With ActiveSheet For i = .Cells(.Rows.Count, 1).End(xlUp).Row To 1 Step -1 t = Right(.Cells(i, 1), 1) If IsNumeric(t) Then If t < 1 And t < 2 Then .Rows(i).Delete End If Next End With End Sub -- KL [MVP - Microsoft Excel] RU: http://www.mvps.ru/Program/Default.aspx ES: http://mvp.support.microsoft.com/?LN=es-es EN: http://mvp.support.microsoft.com/?LN=en-us Profile: https://mvp.support.microsoft.com/pr...A-9E6C73C09A36 "JMay" wrote in message ... Let's try again,,, Here is what I meant to send you - Scratch the other.. Sub DGfoo() Set rng = ActiveSheet.Range(Cells(1, 1), Cells(Cells(Rows.Count, 1).End(xlUp).Row, 1)) For i = rng.Rows.Count To 1 Step -1 t = Right(Cells(i, 1), 1) ' t is numeric If IsNumeric(t) Then MsgBox t & " is numeric" 'proof that t is numeric If t < 1 Or t < 2 Then 'enter 1 and 2 as numeric Rows(i).Delete End If Next End Sub "Don Guillett" wrote: Number vs text. Try this Set rng = ActiveSheet.range(Cells(1, 1), Cells(Cells(Rows.Count, 1).End(xlUp).Row, 1)) For i = rng.Rows.Count To 1 Step -1 t = Right(Cells(i, 1), 1) If t < 1 Or t < 2 Then Rows(i).Delete End If Next -- Don Guillett SalesAid Software "JMay" wrote in message ... With Sheet1 containing in Cells A1:A11 a1 a2 a3 a4 b1 b2 b3 b4 c1 c2 c3 Why is this code Not deleting all and only the ?3's and ?4's Right Now it is deleting ALL/everything !! Hair-pulling here ... Sub foo() Set rng = ActiveSheet.Range(Cells(1, 1), Cells(Cells(Rows.Count, 1).End(xlUp).Row, 1)) lrow = rng.Rows.Count For i = lrow To 1 Step -1 t = Right(Cells(i, 1).Value, 1) If t < "1" Or t < "2" Then Cells(i, 1).EntireRow.Delete End If Next End Sub |
Help with code
KL Thanks - Your code worked, although I thought at first
the AND statement was incorrect - I even changed it to OR before running the first time - But got same (incorrect) results. Using AND it worked,, hummmmm... dummy here, can you splain why the AND is required versus the seeming-correct "OR"? Thanks, Jim "KL" wrote: try this: Sub DGfoo() Dim i As Long, t As Integer With ActiveSheet For i = .Cells(.Rows.Count, 1).End(xlUp).Row To 1 Step -1 t = Right(.Cells(i, 1), 1) If IsNumeric(t) Then If t < 1 And t < 2 Then .Rows(i).Delete End If Next End With End Sub -- KL [MVP - Microsoft Excel] RU: http://www.mvps.ru/Program/Default.aspx ES: http://mvp.support.microsoft.com/?LN=es-es EN: http://mvp.support.microsoft.com/?LN=en-us Profile: https://mvp.support.microsoft.com/pr...A-9E6C73C09A36 "JMay" wrote in message ... Let's try again,,, Here is what I meant to send you - Scratch the other.. Sub DGfoo() Set rng = ActiveSheet.Range(Cells(1, 1), Cells(Cells(Rows.Count, 1).End(xlUp).Row, 1)) For i = rng.Rows.Count To 1 Step -1 t = Right(Cells(i, 1), 1) ' t is numeric If IsNumeric(t) Then MsgBox t & " is numeric" 'proof that t is numeric If t < 1 Or t < 2 Then 'enter 1 and 2 as numeric Rows(i).Delete End If Next End Sub "Don Guillett" wrote: Number vs text. Try this Set rng = ActiveSheet.range(Cells(1, 1), Cells(Cells(Rows.Count, 1).End(xlUp).Row, 1)) For i = rng.Rows.Count To 1 Step -1 t = Right(Cells(i, 1), 1) If t < 1 Or t < 2 Then Rows(i).Delete End If Next -- Don Guillett SalesAid Software "JMay" wrote in message ... With Sheet1 containing in Cells A1:A11 a1 a2 a3 a4 b1 b2 b3 b4 c1 c2 c3 Why is this code Not deleting all and only the ?3's and ?4's Right Now it is deleting ALL/everything !! Hair-pulling here ... Sub foo() Set rng = ActiveSheet.Range(Cells(1, 1), Cells(Cells(Rows.Count, 1).End(xlUp).Row, 1)) lrow = rng.Rows.Count For i = lrow To 1 Step -1 t = Right(Cells(i, 1).Value, 1) If t < "1" Or t < "2" Then Cells(i, 1).EntireRow.Delete End If Next End Sub |
Help with code
If t < 1 Or t < 2 Then .Rows(i).Delete
means that the digit is not equal to 1 or not equal to 2 (at least one is True), so: Ex.1: 1 is equal to 1, but not equal to 2 - the second is True, so delete Ex.2: 2 is not equal to 1, but equal to 2 - the first is True, so delete Ex.3: 3 is not equal to 1, and not equal to 2 - both are True, so delete etc. If t < 1 And t < 2 Then .Rows(i).Delete means that the digit is not equal to 1 and not equal to 2 at the same time (both must necessarily be True) -- KL [MVP - Microsoft Excel] RU: http://www.mvps.ru/Program/Default.aspx ES: http://mvp.support.microsoft.com/?LN=es-es EN: http://mvp.support.microsoft.com/?LN=en-us Profile: https://mvp.support.microsoft.com/pr...A-9E6C73C09A36 "JMay" wrote in message ... KL Thanks - Your code worked, although I thought at first the AND statement was incorrect - I even changed it to OR before running the first time - But got same (incorrect) results. Using AND it worked,, hummmmm... dummy here, can you splain why the AND is required versus the seeming-correct "OR"? Thanks, Jim "KL" wrote: try this: Sub DGfoo() Dim i As Long, t As Integer With ActiveSheet For i = .Cells(.Rows.Count, 1).End(xlUp).Row To 1 Step -1 t = Right(.Cells(i, 1), 1) If IsNumeric(t) Then If t < 1 And t < 2 Then .Rows(i).Delete End If Next End With End Sub -- KL [MVP - Microsoft Excel] RU: http://www.mvps.ru/Program/Default.aspx ES: http://mvp.support.microsoft.com/?LN=es-es EN: http://mvp.support.microsoft.com/?LN=en-us Profile: https://mvp.support.microsoft.com/pr...A-9E6C73C09A36 "JMay" wrote in message ... Let's try again,,, Here is what I meant to send you - Scratch the other.. Sub DGfoo() Set rng = ActiveSheet.Range(Cells(1, 1), Cells(Cells(Rows.Count, 1).End(xlUp).Row, 1)) For i = rng.Rows.Count To 1 Step -1 t = Right(Cells(i, 1), 1) ' t is numeric If IsNumeric(t) Then MsgBox t & " is numeric" 'proof that t is numeric If t < 1 Or t < 2 Then 'enter 1 and 2 as numeric Rows(i).Delete End If Next End Sub "Don Guillett" wrote: Number vs text. Try this Set rng = ActiveSheet.range(Cells(1, 1), Cells(Cells(Rows.Count, 1).End(xlUp).Row, 1)) For i = rng.Rows.Count To 1 Step -1 t = Right(Cells(i, 1), 1) If t < 1 Or t < 2 Then Rows(i).Delete End If Next -- Don Guillett SalesAid Software "JMay" wrote in message ... With Sheet1 containing in Cells A1:A11 a1 a2 a3 a4 b1 b2 b3 b4 c1 c2 c3 Why is this code Not deleting all and only the ?3's and ?4's Right Now it is deleting ALL/everything !! Hair-pulling here ... Sub foo() Set rng = ActiveSheet.Range(Cells(1, 1), Cells(Cells(Rows.Count, 1).End(xlUp).Row, 1)) lrow = rng.Rows.Count For i = lrow To 1 Step -1 t = Right(Cells(i, 1).Value, 1) If t < "1" Or t < "2" Then Cells(i, 1).EntireRow.Delete End If Next End Sub |
Help with code
KL - Thanks, I better understand..now
when testing <1 or <2 = Result 4 T or T = T 3 T or T = T 2 T or F = F 1 F or T = F I remember now why I did'nt go into programming 30+ years ago - when I was in a class much like this; I walked out "drunk-as-a-sailor" - from the confusion.. Didn't deal with it then, so must deal with it now. LOL In my Senior years Thanks again for your generous examples, only hope I can remember and recognize the next time I'm involved with the same type thing... Jim May "KL" wrote: If t < 1 Or t < 2 Then .Rows(i).Delete means that the digit is not equal to 1 or not equal to 2 (at least one is True), so: Ex.1: 1 is equal to 1, but not equal to 2 - the second is True, so delete Ex.2: 2 is not equal to 1, but equal to 2 - the first is True, so delete Ex.3: 3 is not equal to 1, and not equal to 2 - both are True, so delete etc. If t < 1 And t < 2 Then .Rows(i).Delete means that the digit is not equal to 1 and not equal to 2 at the same time (both must necessarily be True) -- KL [MVP - Microsoft Excel] RU: http://www.mvps.ru/Program/Default.aspx ES: http://mvp.support.microsoft.com/?LN=es-es EN: http://mvp.support.microsoft.com/?LN=en-us Profile: https://mvp.support.microsoft.com/pr...A-9E6C73C09A36 "JMay" wrote in message ... KL Thanks - Your code worked, although I thought at first the AND statement was incorrect - I even changed it to OR before running the first time - But got same (incorrect) results. Using AND it worked,, hummmmm... dummy here, can you splain why the AND is required versus the seeming-correct "OR"? Thanks, Jim "KL" wrote: try this: Sub DGfoo() Dim i As Long, t As Integer With ActiveSheet For i = .Cells(.Rows.Count, 1).End(xlUp).Row To 1 Step -1 t = Right(.Cells(i, 1), 1) If IsNumeric(t) Then If t < 1 And t < 2 Then .Rows(i).Delete End If Next End With End Sub -- KL [MVP - Microsoft Excel] RU: http://www.mvps.ru/Program/Default.aspx ES: http://mvp.support.microsoft.com/?LN=es-es EN: http://mvp.support.microsoft.com/?LN=en-us Profile: https://mvp.support.microsoft.com/pr...A-9E6C73C09A36 "JMay" wrote in message ... Let's try again,,, Here is what I meant to send you - Scratch the other.. Sub DGfoo() Set rng = ActiveSheet.Range(Cells(1, 1), Cells(Cells(Rows.Count, 1).End(xlUp).Row, 1)) For i = rng.Rows.Count To 1 Step -1 t = Right(Cells(i, 1), 1) ' t is numeric If IsNumeric(t) Then MsgBox t & " is numeric" 'proof that t is numeric If t < 1 Or t < 2 Then 'enter 1 and 2 as numeric Rows(i).Delete End If Next End Sub "Don Guillett" wrote: Number vs text. Try this Set rng = ActiveSheet.range(Cells(1, 1), Cells(Cells(Rows.Count, 1).End(xlUp).Row, 1)) For i = rng.Rows.Count To 1 Step -1 t = Right(Cells(i, 1), 1) If t < 1 Or t < 2 Then Rows(i).Delete End If Next -- Don Guillett SalesAid Software "JMay" wrote in message ... With Sheet1 containing in Cells A1:A11 a1 a2 a3 a4 b1 b2 b3 b4 c1 c2 c3 Why is this code Not deleting all and only the ?3's and ?4's Right Now it is deleting ALL/everything !! Hair-pulling here ... Sub foo() Set rng = ActiveSheet.Range(Cells(1, 1), Cells(Cells(Rows.Count, 1).End(xlUp).Row, 1)) lrow = rng.Rows.Count For i = lrow To 1 Step -1 t = Right(Cells(i, 1).Value, 1) If t < "1" Or t < "2" Then Cells(i, 1).EntireRow.Delete End If Next End Sub |
Help with code
"JMay" wrote in message ...
KL - Thanks, I better understand..now when testing <1 or <2 = Result 4 T or T = T 3 T or T = T 2 T or F = F 1 F or T = F Yep, something like this. Only the equation for your example is "<1 and <2 = Result" i.e.: when testing <1 and <2 = Result 4 T and T = T 3 T and T = T 2 T and F = F 1 F and T = F when testing <1 or <2 = Result 4 T or T = T 3 T or T = T 2 T or F = T 1 F or T = T -- KL [MVP - Microsoft Excel] RU: http://www.mvps.ru/Program/Default.aspx ES: http://mvp.support.microsoft.com/?LN=es-es EN: http://mvp.support.microsoft.com/?LN=en-us Profile: https://mvp.support.microsoft.com/pr...A-9E6C73C09A36 |
Help with code
thanks again for your clarification
"KL" wrote in message : "JMay" wrote in message ... KL - Thanks, I better understand..now when testing <1 or <2 = Result 4 T or T = T 3 T or T = T 2 T or F = F 1 F or T = F Yep, something like this. Only the equation for your example is "<1 and <2 = Result" i.e.: when testing <1 and <2 = Result 4 T and T = T 3 T and T = T 2 T and F = F 1 F and T = F when testing <1 or <2 = Result 4 T or T = T 3 T or T = T 2 T or F = T 1 F or T = T -- KL [MVP - Microsoft Excel] RU: http://www.mvps.ru/Program/Default.aspx ES: http://mvp.support.microsoft.com/?LN=es-es EN: http://mvp.support.microsoft.com/?LN=en-us Profile: https://mvp.support.microsoft.com/pr...A-9E6C73C09A36 |
| All times are GMT +1. The time now is 04:13 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com