Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete rows between two dates
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete rows between two dates
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete rows between two dates
That's it (I think) however I get message Error 13, Type mismatch.
Any ideas on how to fix. +++++++++++ "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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete rows between two dates
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete rows between two dates
Yes, I'm still getting it. Debug highlights all of your latest code
I appreciate your help here. Could it be the format of my dates which are exported from Outlook |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete rows between two dates
No, it might not work if the dates were of an odd format, it wouldn't throw
the code off like that. Did you wrap that code up in a macro? -- HTH RP (remove nothere from the email address if mailing direct) wrote in message oups.com... Yes, I'm still getting it. Debug highlights all of your latest code I appreciate your help here. Could it be the format of my dates which are exported from Outlook |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete rows between two dates
consider these:
'use autofilter to hide the desired dates to keep then delete all visible rows 'change cells(finalrow,7) to however many columns you have 'change field:=1 to whichever is your date column finalrow = range("A65536").end(xlup).row BOM = "<" & beforenow EOM = "" & beforenow+30 With Range(Cells(1, 1), Cells(finalrow, 7)) 'change 7 to however many columns you have ..AutoFilter Field:=1, Criteria1:=BOM, Operator:=xlOr, Criteria2:=EOM ..Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count) _ ..SpecialCells(xlCellTypeVisible).EntireRow.Delete End With ActiveSheet.AutoFilterMode = False 'turn off autofilter 'now remove rows with col(A)=perm and col(c)=client enddate for lrow = finalrow to 2 step -1 if trim(lcase(range("A"&lrow).value))="client enddate" _ and trim(lcase(range("C"&lrow).value))="perm" _ then rows.delete next lrow =============================== ' 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 ============= "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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete rows between two dates
Bob,
The complete macro is :- Sub Outlook_Reminders_amended_current() ' subtract CALC Range("e2").Select ActiveCell.FormulaR1C1 = "=IF((RC[-2])="""",""1"",IF((RC[-4])=""Birthday"",(RC[-2])-4,(RC[-2])-42))" ' REMINDER COLUMN Dim AmyRange As Range Set AmyRange = Worksheets("Sheet1").Range("d2") AmyRange.Formula = "=date(year(e2),month(e2),day(e2))" ' CALC COLUMN FILL Call LastCell(Sheet1) ActiveCell.Name = "lastqq" ' Selection.SpecialCells(xlCellTypeLastCell).Select ActiveCell.Offset(0, -1).Select ActiveCell.Name = "lastbirth" Range("e2").Select Selection.AutoFill Destination:=Range("e2:lastqq"), Type:=xlFillDefault ' REMINDER COLUMN FILL ' Selection.SpecialCells(xlCellTypeLastCell).Select Call LastCell(Sheet1) ActiveCell.Name = "lastqq" ActiveCell.Offset(0, -1).Select ActiveCell.Name = "lastxx" Range("d2").Select Selection.AutoFill Destination:=Range("d2:lastxx"), Type:=xlFillDefault '+++++ copy and paste values +++++++++++++++++++++ Columns("D:D").Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False Selection.NumberFormat = "dd-mm-yy" '++++++++++++++++++++++++++++++++++++++++++++ ' DELETED PERM AND CLIENT ENDDATE 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") Or _ (.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 ' &&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& &&&&&&&&& ' 'SORTS ONLY THOSE WITH DATES ' With Range("c1:c200") ' x = .Find("/").Row 'MsgBox x 'End With 'With Range("c11:c200") 'y = .Find("").Row - 1 'MsgBox y 'Rows(x & ":" & y).Select ' Selection.Sort Key1:=Range("D:D"), Order1:=xlAscending, Header:=xlGuess, _ ' OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ ' DataOption1:=xlSortNormal ' End With '++++++++++++++++++++++++++++++ ' ActiveWorkbook.Save Range("a1").Select MsgBox "YELLOW = Client End Date, BLUE = Details missing, PURPLE = Due within 28 days" End Sub |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete rows between two dates
Jef, your code deletes ALL lines.
The < and dates show up correctly in a msgbox |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete rows between two dates
I am convinced that the exported "dates" from Outlook are text. Can you
oblige by giving me code which will change column C from text to dates. I have searched Google Groups for such code to no avail. |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete rows between two dates
Maybe
ElseIf (Trim(LCase(.Cells(Lrow, "C").Value)) = "perm" And _ .Cells(Lrow, "A").Value = "Client EndDate") Or _ (CDate.Cells(Lrow, "D").Value) < Date Or _ CDate(.Cells(Lrow, "D").Value) Date + 30) Then .Rows(Lrow).Delete -- HTH RP (remove nothere from the email address if mailing direct) wrote in message ups.com... I am convinced that the exported "dates" from Outlook are text. Can you oblige by giving me code which will change column C from text to dates. I have searched Google Groups for such code to no avail. |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete rows between two dates
Got an "expected ( " error, changed your code to
ElseIf (Trim(LCase(.Cells(Lrow, "C").Value)) = "perm" And _ .Cells(Lrow, "A").Value = "Client EndDate") Or _ CDate(.Cells(Lrow, "D").Value) < Date Or _ CDate(.Cells(Lrow, "D").Value) Date + 30 Then .Rows(Lrow).Delete still getting "Type Mismatch" I have managed to write code to change text to dates, thanks. Should I give up and start growing vegetables ? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to delete rows when List toolbar's "delete" isnt highlighted? | Excel Worksheet Functions | |||
Delete Rows whose value is not between 2 dates | Excel Programming | |||
Delete rows with particular dates present | Excel Programming | |||
Delete Rows with particular dates | Excel Programming | |||
Delete every 3rd row, then delete rows 2-7, move info f/every 2nd row up one to the end and delete the row below | Excel Programming |