Developer Forum

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???


--
DKY
------------------------------------------------------------------------
DKY's Profile: http://www.yqcomputer.com/
View this thread: http://www.yqcomputer.com/

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.

---
Regards,
Norman

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

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.

Norman Jones Wrote:



--
DKY
------------------------------------------------------------------------
DKY's Profile: http://www.yqcomputer.com/
View this thread: http://www.yqcomputer.com/

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?

Mike Fogleman Wrote:



--
DKY
------------------------------------------------------------------------
DKY's Profile: http://www.yqcomputer.com/
View this thread: http://www.yqcomputer.com/

@ 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
--------------------


--
DKY
------------------------------------------------------------------------
DKY's Profile: http://www.yqcomputer.com/
View this thread: http://www.yqcomputer.com/

Hi DKY,

I misread your original request.

Change


to:

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


---
Regards,
Norman

Hi DKY,

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


to

For i = Lrow To 2 Step -1

---
Regards,
Norman

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

--
steveB

Remove "AYN" from email to respond

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?

STEVE BELL Wrote:


--
DKY
------------------------------------------------------------------------
DKY's Profile: http://www.yqcomputer.com/
View this thread: http://www.yqcomputer.com/

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

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

Mike F

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()


--
DKY
------------------------------------------------------------------------
DKY's Profile: http://www.yqcomputer.com/
View this thread: http://www.yqcomputer.com/

Hi DKY,

What is the error message you receive?


---
Regards,
Norman

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
------------------------------------------------------------------------
DKY's Profile: http://www.yqcomputer.com/
View this thread: http://www.yqcomputer.com/