Proper way to write a conditional statement with ands

[DKY]

How would I translate this into VBS?? I know what I want to do, but
don't know the proper way to do this.

If the value in column K<>"" AND <>"0000" AND the value in column
M>"(Now()-7)" then delete the row.

Help???

 

[Norman Jones]

Hi DKY,

Try:

Public Sub Test03()

Dim sh As Worksheet
Dim i As Long
Dim Lrow As Long
Dim rng As Range

Set sh = Sheets("Sheet1") '<<====== CHANGE

Lrow = Cells(Rows.Count, "K").End(xlUp).Row

For i = Lrow To 1 Step -1
Set rng = Range("K" & i)
If Not IsEmpty(rng) Then
If rng.Value <> "0000" Then
If rng.Offset(0, 2).Value = Date - 7 Then
rng.EntireRow.Delete
End If
End If
End If
Next

End Sub

Change Sheet1 to the name of your worksheet.

Until you are happy that this macro properly reflects your intentions, run
it on a copy of your workbook.

 

[Mike Fogleman]

If Range("K1").Value <> "" And Range("K1").Value <> "0000" And _
Range("M1").Value > Now - 7 Then Range("K1").EntireRow.Delete

This will delete Row 1 when the 3 conditions are met.

Mike F

 

[DKY]

Thanks for the quick response Norman, but the code below doesn't seem to
do anything in column M. How would I adjust it so that it compares the
date in column M, not in K and still have it compare values in column K
such as the blank value "" and the 0000 value "0000". Thanks in
advance.

 

[DKY]

Mike,
This code seems to do what I want it to do but how would I get this to
work through all the rows until it gets to the last row with any data?
I also noticed that the code would get rid of my header so I started it
on K2 instead. In order to achieve the ability to have the macro go on
down the rows until there are none left I think I would have to do a Do
While loop. Is that correct?

 

[DKY]

@ Mike

Here's what I got and its not working, Its like in an endless loop or
something, it just freezes up my screen.


Code:
--------------------
Public Sub conditional()
Dim i
i = "2"
Do While Range("A" & i).Value <> ""
If Range("K" & i).Value <> "" And Range("K" & i).Value <> "0000" And Range("M" & i).Value > Now - 9 Then
Range("K" & i).EntireRow.Delete
i = (i = 1)
End If
Loop
End Sub

 

[Norman Jones]

Hi DKY,

I misread your original request.

Change

to:

If rng.Offset(0, 2).Value > Date - 7 Then

 

[Norman Jones]

Hi DKY,

Given your reference to a header row in your response to Mike, amend:

to

For i = Lrow To 2 Step -1

 

[STEVE BELL]

i does not increment unless the if statement runs,
so it will stay = 2 forever

put i = i + 1
after the End If
and remove the quotes from i = "2"

Public Sub conditional()
Dim i as long
i = 2
Do While Range("A" & i).Value <> ""
If Range("K" & i).Value <> "" And Range("K" & i).Value <> "0000" And
Range("M" & i).Value > Now - 9 Then
Range("K" & i).EntireRow.Delete
End If
i = i + 1
Loop
End Sub

 

[DKY]

Okay, thanks Steve. This now runs but when it runs its quirky. Just to
see exactly what it was deleting I had it put an X in the far right
column instead of delete. Then I colored and sorted. It seems like
this does ignore anything with a blank or a 0000 in column K but it
deletes everything else, no matter what the value of column M is. The
values in column M were set up as general so I changed them to date to
see if that would make a difference but it didn't. They are in this
format, mm/dd/yy in every cell and I can't figure out why it deletes
all of them, even if they are greater than the specified date. I even
had it put the Now- 9 in the cell instead of the X and the dates are
coming out okay. It doesn't make sense to me. Help?

 

[Mike Fogleman]

Actually Norman has the better loop method because whenever you delete rows,
you should always start at the bottom of the list and work your way up. Here
is Norman's original loop with the changes for stopping before the Header
and Date comparison.

Public Sub Test03()

Dim sh As Worksheet
Dim i As Long
Dim Lrow As Long
Dim rng As Range

Set sh = Sheets("Sheet1") '<<====== CHANGE

Lrow = Cells(Rows.Count, "K").End(xlUp).Row 'Finds the number of rows
in column K - if this is not a good column to determine the length of your
list then use a column that will.

For i = Lrow To 2 Step -1 'Loops from bottom to top- stops @ row 2.
Set rng = Range("K" & i)
If Not IsEmpty(rng) Then
If rng.Value <> "0000" Then
If rng.Offset(0, 2).Value > Date - 7 Then
rng.EntireRow.Delete
End If
End If
End If
Next

End Sub

Change Sheet1 to the name of your worksheet.

Until you are happy that this macro properly reflects your intentions, run
it on a copy of your workbook.

Mike F

 

[Mike Fogleman]

I noticed you changed the date comparison to 9. You will need to do that in
the below code also.

Mike F

 

[DKY]

Okay, I went to the code that Norman had posted and tried running it.
It says compile error: syntax error. Then it highlights the first
line in the code
Public Sub Test03()

 

[Norman Jones]

Hi DKY,

What is the error message you receive?

 

[DKY]

Found it, nevermind. When I copied and pasted it, it put carriage
returns in the comment so once I got rid of those it was working fine.
Problem is, it seems to be deleting too many rows. When I do it
manually on my spreadsheet I get 783 rows. When I open the spreadsheet
and run the macro I end up with 578 rows left. I've got to play with it
some more. Maybe I can figure out a way to have it put the letter x in
the last column (column x) instead of deleting it so that I can see
which one's its going to delete. I will try that later.

 

[DKY]

Okay, I've modified the code to add the blanks part. But the only thing
its doing that I can't seem to figure out is the same thing it was doing
with Mike's code above. I must be doing something wrong here. Its not
deleting the rows where k=0000 and its not deleting the rows where k is
blank which is what I want, that part is perfect. But when k has a
value that's not 0000 I wanted it to delete those rows where M > Date -
9. Its almost as if it doesnt even look at column M and it deletes the
row anyway. Here's my code right now (Ignore the put the letter x in
the x column part)


Code:
--------------------
Public Sub Test03()

Dim sh As Worksheet
Dim i As Long
Dim Lrow As Long
Dim rng As Range

Set sh = Sheets("S2661060") '<<====== CHANGE

Lrow = Cells(Rows.Count, "A").End(xlUp).Row
'Finds the number of rows in column K - if this is not a
'good column to determine the length of your list then use a column that will.

For i = Lrow To 2 Step -1 'Loops from bottom to top- stops @ row 2.
Set rng = Range("K" & i)
If Not IsEmpty(rng) Then
If rng.Value <> "0000" And rng.Value <> "" Then
If rng.Offset(0, 2).Value > Now - 9 Then
'rng.EntireRow.Delete

'this will put the letter x in rows that are selected for deletion
Range("X" & i).Select
ActiveCell.FormulaR1C1 = "x"
With ActiveCell.Characters(Start:=1, Length:=1).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 1
End With
'end put the letter x in rows that are selected for deletion

End If
End If
End If
Next

End Sub

 

[Norman Jones]

Hi DKY,

To check operation, comment out the line:

rng.EntireRow.Delete

and inssert immdiately after the new line:

rng.EntireRow.Interior.ColorIndex = 36

When you run the procedure, instead of deleting rows, it will color them.

If, and when, you are happy, delete the new line and uncomment the delete
line.

 

[DKY]

That just colored the rows that my x's are in. Either way, for some
reason its like it doesn't recognize column M. I don't get it.

 

[Norman Jones]

Hi DKY,

If you want to send a copy of your workbook, I will try to resolve.

Delete/replace any sensitive data. Mark the rows that meet your deletion
criteria.

nXorman_jXones@btXconnectDOTcom

(replace dot and remove each X) :

 

[DKY]

I just emailed it to you Norman. Thanks for taking the time to look at
it.