Proper way to write a conditional statement with ands

[Norman Jones]

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!

 

[DKY]

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]

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

 

[Norman Jones]

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.

 

[DKY]

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