Help with code

[JMay]

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

 

[JMay]

Whoops, happened to call my Subject Help with code ALSO!!!

 

[Don Guillett]

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

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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

 

[KL]

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/profile=AB32F8EE-8ED3-4AB9-ADDA-9E6C73C09A36

 

[Guest]

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

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/profile=AB32F8EE-8ED3-4AB9-ADDA-9E6C73C09A36

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

 

[KL]

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/profile=AB32F8EE-8ED3-4AB9-ADDA-9E6C73C09A36

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

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/profile=AB32F8EE-8ED3-4AB9-ADDA-9E6C73C09A36

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

:

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
(e-mail address removed)
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

 

[Guest]

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

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/profile=AB32F8EE-8ED3-4AB9-ADDA-9E6C73C09A36

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

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/profile=AB32F8EE-8ED3-4AB9-ADDA-9E6C73C09A36


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

:

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
(e-mail address removed)
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

 

[KL]

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/profile=AB32F8EE-8ED3-4AB9-ADDA-9E6C73C09A36

 

[JMay]

thanks again for your clarification

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/profile=AB32F8EE-8ED3-4AB9-ADDA-9E6C73C09A36