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.excelforum.com/member.php...o&userid=14515
View this thread: http://www.excelforum.com/showthread...hreadid=387731

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



"DKY" wrote in message
...

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.excelforum.com/member.php...o&userid=14515
View this thread: http://www.excelforum.com/showthread...hreadid=387731

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" wrote in message
...

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.excelforum.com/member.php...o&userid=14515
View this thread: http://www.excelforum.com/showthread...hreadid=387731

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



"DKY" wrote in
message
...

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.excelforum.com/member.php...o&userid=14515
View this thread:
http://www.excelforum.com/showthread...hreadid=387731



--
DKY
------------------------------------------------------------------------
DKY's Profile: http://www.excelforum.com/member.php...o&userid=14515
View this thread: http://www.excelforum.com/showthread...hreadid=387731

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:
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" wrote in
message
...

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.excelforum.com/member.php...o&userid=14515
View this thread:
http://www.excelforum.com/showthread...hreadid=387731



--
DKY
------------------------------------------------------------------------
DKY's Profile: http://www.excelforum.com/member.php...o&userid=14515
View this thread: http://www.excelforum.com/showthread...hreadid=387731

@ 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.excelforum.com/member.php...o&userid=14515
View this thread: http://www.excelforum.com/showthread...hreadid=387731

Hi DKY,

I misread your original request.

Change

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

to:

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


---
Regards,
Norman



"DKY" wrote in message
...

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

Hi DKY,

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

For i = Lrow To 1 Step -1

to

For i = Lrow To 2 Step -1

---
Regards,
Norman



"DKY" wrote in message
...

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



"DKY" wrote in
message
...

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.excelforum.com/member.php...o&userid=14515
View this thread:
http://www.excelforum.com/showthread...hreadid=387731



--
DKY
------------------------------------------------------------------------
DKY's Profile:
http://www.excelforum.com/member.php...o&userid=14515
View this thread: http://www.excelforum.com/showthread...hreadid=387731

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
"DKY" wrote in message
...

@ 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.excelforum.com/member.php...o&userid=14515
View this thread: http://www.excelforum.com/showthread...hreadid=387731

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



--
DKY
------------------------------------------------------------------------
DKY's Profile: http://www.excelforum.com/member.php...o&userid=14515
View this thread: http://www.excelforum.com/showthread...hreadid=387731

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

"DKY" wrote in message
...

@ 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.excelforum.com/member.php...o&userid=14515
View this thread: http://www.excelforum.com/showthread...hreadid=387731

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

Mike F
"Mike Fogleman" wrote in message
...
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

"DKY" wrote in message
...

@ 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.excelforum.com/member.php...o&userid=14515
View this thread:
http://www.excelforum.com/showthread...hreadid=387731

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.excelforum.com/member.php...o&userid=14515
View this thread: http://www.excelforum.com/showthread...hreadid=387731

Hi DKY,

What is the error message you receive?


---
Regards,
Norman



"DKY" wrote in message
...

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.excelforum.com/member.php...o&userid=14515
View this thread: http://www.excelforum.com/showthread...hreadid=387731

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.excelforum.com/member.php...o&userid=14515
View this thread: http://www.excelforum.com/showthread...hreadid=387731

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


--
DKY
------------------------------------------------------------------------
DKY's Profile: http://www.excelforum.com/member.php...o&userid=14515
View this thread: http://www.excelforum.com/showthread...hreadid=387731

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.


---
Regards,
Norman



"DKY" wrote in message
...

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.excelforum.com/member.php...o&userid=14515
View this thread: http://www.excelforum.com/showthread...hreadid=387731

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.


--
DKY
------------------------------------------------------------------------
DKY's Profile: http://www.excelforum.com/member.php...o&userid=14515
View this thread: http://www.excelforum.com/showthread...hreadid=387731

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

---
Regards,
Norman



"DKY" wrote in message
...

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.


--
DKY
------------------------------------------------------------------------
DKY's Profile:
http://www.excelforum.com/member.php...o&userid=14515
View this thread: http://www.excelforum.com/showthread...hreadid=387731

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


--
DKY
------------------------------------------------------------------------
DKY's Profile: http://www.excelforum.com/member.php...o&userid=14515
View this thread: http://www.excelforum.com/showthread...hreadid=387731

Hi DKY,

I received your workbook.

The data in column M on Sheet S2661060 are not dates and, hence, the date
test in my suggested procedure failed.

Once the dates issue was resolved, my procedure ran without problem.

I have, therefore, added some initial code to convert column M to
recognisable dates.

I have also added a function to verify that the correct sheet is open.

The updated code is:

'===============================
Public Sub DeleteDataRows()

Dim sh As Worksheet
Dim i As Long
Dim Lrow As Long
Dim Rng As Range
Dim Rng1 As Range
Const shtName As String = "S2661060" '<<=== CHANGE??

On Error GoTo XIT

If Not SheetExists(shtName) Then
MsgBox "No " & shtName & " S2661060 sheet found" _
& vbNewLine & _
"Check that correct workbook is active!", _
vbCritical, _
"Check Workbook"
Exit Sub
End If

Set sh = Sheets(shtName)

With sh
Set Rng1 = Intersect(.UsedRange, .Columns("M"))
End With

Set Rng1 = Rng1.Offset(1).Resize(Rng1.Rows.Count - 1, 1)

Application.ScreenUpdating = False

With Rng1
.Value = .Value
.NumberFormat = "mmm-dd-yy"
End With

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

For i = Lrow To 2 Step -1
Set Rng = Range("K" & i)
If Not IsEmpty(Rng1) Then
If Rng.Value < "0000" Then
If Rng.Offset(0, 2).Value Date - 7 Then
' Rng.EntireRow.Delete '<<=== REINSTATE
Rng(1, 2).Interior.ColorIndex = 36 '<<==DELETE
End If
End If
End If
Next

XIT:
Application.ScreenUpdating = True

End Sub
'<<===============================
'===============================
Function SheetExists(SName As String, _
Optional ByVal WB As Workbook) As Boolean
'Chip Pearson
On Error Resume Next
If WB Is Nothing Then Set WB = ActiveWorkbook
SheetExists = CBool(Len(WB.Sheets(SName).Name))
End Function
'<<===============================


The procedure is written to allow you readily to satisfy yourself that it
does what you want and expect.

So, rather than deleting rows, the corresponding column M values are
highlighted.

Once you are happy that everthing is as it should be, delete the line:

rng(1, 2).Interior.ColorIndex = 36

and uncomment the prededing line:

Rng.EntireRow.Delete

This done, save the file, and copy/paste the code (including the function)
to your Personal.xls.

Finally, to check the code with the marked data you sent me, you will need
either to:

Turm your clock back by four days, or

Change (temporarily) the date condition of Date - 7 to
Date - 3

In any event, restore the clock or date condition!

---
Regards,
Norman


"DKY" wrote in message
...

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


--
DKY
------------------------------------------------------------------------
DKY's Profile:
http://www.excelforum.com/member.php...o&userid=14515
View this thread: http://www.excelforum.com/showthread...hreadid=387731

I copied and pasted this into my personal workbook and ran the macro. I
changed my date on my pc to the 14th of July and it highlights anything
with a value in K. No matter what the date is in M. Its set to
highlight anything with a date Date - 7 in column M, (which according
to my calculations is any date that's greater than July 7th. Problem is
it still highlights those whose dates are July 5th. That's not greater,
that's less than. Does it highlight those with those dates on your end
when you run it? Or am I doing something wrong??


--
DKY
------------------------------------------------------------------------
DKY's Profile: http://www.excelforum.com/member.php...o&userid=14515
View this thread: http://www.excelforum.com/showthread...hreadid=387731

I'm sorry, I copied and pasted from the first post. There must be an
edit in the code and that's why there's two posts. The second one
seems to work fine. I'll test it out more this morning when I get to
work. Thanks Norm


--
DKY
------------------------------------------------------------------------
DKY's Profile: http://www.excelforum.com/member.php...o&userid=14515
View this thread: http://www.excelforum.com/showthread...hreadid=387731

Hi DKY,

The code runs without problem for me using your copy workbook.

If you want me to send a working version of your workbook, mail me.

---
Regards,
Norman



"DKY" wrote in message
...

I'm sorry, I copied and pasted from the first post. There must be an
edit in the code and that's why there's two posts. The second one
seems to work fine. I'll test it out more this morning when I get to
work. Thanks Norm


--
DKY
------------------------------------------------------------------------
DKY's Profile:
http://www.excelforum.com/member.php...o&userid=14515
View this thread: http://www.excelforum.com/showthread...hreadid=387731

The code runs flawless, thank you very much Norman. I had copied the
wrong code above. My mistake, thanks again.


--
DKY
------------------------------------------------------------------------
DKY's Profile: http://www.excelforum.com/member.php...o&userid=14515
View this thread: http://www.excelforum.com/showthread...hreadid=387731