Delete rows between two dates
Yes, you are correct. For an OR, use
ElseIf (Trim(LCase(.Cells(Lrow, "C").Value)) = "perm" And _
.Cells(Lrow, "A").Value = "Client EndDate") Or _
(.Cells(Lrow, "D").Value < Date Or _
.Cells(Lrow, "D").Value Date + 30) Then
.Rows(Lrow).Delete
Are you still getting error 13? I don't get that. Where does it happen?
--
HTH
RP
(remove nothere from the email address if mailing direct)
"jocker" wrote in message
. nl...
Does this code delete rows only if "perm" and "Client Enddate" and
outwith
the 2 dates.
I would like to delete rows if
a) "Perm" and "Client EndDate" or
b) outwith the 2 dates.
"Bob Phillips" wrote in message
...
This tests a date in column D
Dim Lrow As Long
Dim CalcMode As Long
Dim StartRow As Long
Dim EndRow As Long
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With
With ActiveSheet
.DisplayPageBreaks = False
StartRow = 1
EndRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For Lrow = EndRow To StartRow Step -1
If IsError(.Cells(Lrow, "A").Value) Then ' Do nothing
'++++++++++++++++++++++
' This will delete each row with the Values in Columns A and C, case
insensitive.
ElseIf Trim(LCase(.Cells(Lrow, "C").Value)) = "perm" And _
.Cells(Lrow, "A").Value = "Client EndDate" And _
(.Cells(Lrow, "D").Value < Date Or _
.Cells(Lrow, "D").Value Date + 30) Then
.Rows(Lrow).Delete
'+++++++++++++++++++++++++++++++++++++++++
End If
Next
End With
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With
--
HTH
RP
(remove nothere from the email address if mailing direct)
"jocker" wrote in message
. nl...
I am using this code to delete rows which contain "perm" and "Client
EndDate" .
I also wish to delete rows before now and after now + 30 days.
I've tried all sorts of combinations without success. Can somwone help
a
relative newcomer to VBA
Dim Lrow As Long
Dim CalcMode As Long
Dim StartRow As Long
Dim EndRow As Long
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With
With ActiveSheet
.DisplayPageBreaks = False
StartRow = 1
EndRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For Lrow = EndRow To StartRow Step -1
If IsError(.Cells(Lrow, "A").Value) Then ' Do nothing
'++++++++++++++++++++++
' This will delete each row with the Values in Columns A and C, case
insensitive.
ElseIf Trim(LCase(.Cells(Lrow, "C").Value)) = "perm" And _
.Cells(Lrow, "A").Value = "Client EndDate" Then
..Rows(Lrow).Delete
'+++++++++++++++++++++++++++++++++++++++++
End If
Next
End With
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With
|