I have a macro that I would like to edit. Currently the code executes to the point where I have to intervene manually to complete the task. Is there VBA that I can place into the macro that will, after identifying the specific column, delete all rows that contain a date greater than <today>? The macro would have already performed the sort of <date> in ascending order. The actual beginning row number of rows to be deleted will vary depending on the amount of entries for any given date of worksheet generation.
Delete Rows, Based on Date
-
-
-
Re: Delete Rows, Based on Date
Hi dave
see this code...this should help you out
Code
Display MoreSub delrows() Dim endrow As Integer On Error Resume Next endrow = Sheets("sheet1").Range("D65536").End(xlUp).Row For i = endrow To 1 Step -1 tdate = Cells(i, 4).Value If IsDate(tdate) = True And tdate > Date Then Cells(i, 4).EntireRow.Delete End If Next i End Sub
this is set for Col D..you may change range references to suit your requirements
HTH
pangolin
-
-
Re: Delete Rows, Based on Date
Copy pangolin code and add a button or call in from the other code depends on how You use the code You had.
- or -
Copy and paste
[vba]
Dim endrow As Integer
On Error Resume Next
endrow = Sheets("sheet1").Range("D65536").End(xlUp).Row
For i = endrow To 1 Step -1
tdate = Cells(i, 4).Value
If IsDate(tdate) = True And tdate > Date Then
Cells(i, 4).EntireRow.Delete
End If
Next i
[/vba]
Over the code You have between
[vba]
Sub OutstandingOrders()
'---- new code starts here' --- new code in here
'---- new code ends here
End Sub
[/vba]
This assumes two things1 - You take a back Up (copy) of Your old codes just in case, and a back up copy of Your WorkBook just in case
2 - You have nothing else in that section of code ie You only want the delete stuff
jiuk
-
Re: Delete Rows, Based on Date
Thanks, Jack.
I made two attempts at placing the code, one between Sub Outstanding Orders() and the original macro code, and the other after the original macro code. The original macro code still runs in either instance, however, not the code provided by pangolin. Sorry, I have not been successful using the "Code tag" otherwise I could present an example.
-
-
Re: Delete Rows, Based on Date
Do You need to assign the button You press again to run the codes
jiuk
-
Re: Delete Rows, Based on Date
Jack, Pangolin:
I get it now.
Thanks, this is perfect.
How do I mark this thread as successful?
-
Re: Delete Rows, Based on Date
So far, so good. Thanks for all the help. I would like to add to this code so that rows with Column C that are blank, i.e., contain no date are deleted.
Code
Display MoreSub delrows() endrow = Sheets("Management Operations").Range("C65536").End(xlUp).Row For i = endrow To 1 Step -1 tdate = Cells(i, 3).Value If IsDate(tdate) = True And tdate > Date Then Cells(i, 3).EntireRow.Delete End If Next i Cells.Select Cells.EntireColumn.AutoFit Range("A2").Select Columns("A:A").ColumnWidth = 10.43 Columns("D:D").ColumnWidth = 26.57 Columns("E:E").ColumnWidth = 5.57 Columns("F:F").ColumnWidth = 7.14 Columns("H:H").ColumnWidth = 9.43 Columns("I:M").Select Selection.Delete Shift:=xlToLeft Range("I1").Select Columns("I:I").ColumnWidth = 11.86 Columns("J:J").Select Selection.Delete Shift:=xlToLeft Selection.ColumnWidth = 15.71 Columns("M:AB").Select Selection.Delete Shift:=xlToLeft ActiveWindow.SmallScroll ToRight:=-6 Range("A2").Select Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic End Sub [CODE]CODE
-
Re: Delete Rows, Based on Date
Dave196
MemberPlease do not make the post as solved - plays arround and messes up the search engine on OzGrid, besides give me a lot more work to do
Cheers buddy - oh just leave the feed with a thank You to whom helps solve the questions and say what You need to
jiuk
-
Re: Delete Rows, Based on Date
What should be the macro to take 'Date' from a user (code should prompt user to give specific date) or read date in Cell O2, and delete entire ROWS from Column O, where similar date is found, including Row 2 where the code read the date from cell O2, or taken from a user through prompt.
-
-
Re: Delete Rows, Based on Date
[h=2]Delete Rows, Based on Date[/h][INDENT]I have a macro that I would like to edit. Currently the code executes to the point where I have to intervene manually to complete the task. Is there VBA that I can place into the macro that will, after identifying the specific column, delete all rows smaller or equal with yesterday 06:00 AM and greater than today 06:00. I tried here something, but it´s not what i would to have. Could you help me please ? Thanks in advance
Sub DeleteRowBasedOnDateandTimeRange()
Dim RowToTest As Long
Dim ws As Worksheet
Dim dts As Long, dte As Long, tm As Stringdts = Date - 1
dte = Date
tm = #6:00:00 AM#For Each ws In ThisWorkbook.Sheets
For RowToTest = ws.Cells(Rows.Count, 1).End(xlUp).Row To 1 Step -1
With ws.Cells(RowToTest, 1)
If .Value <= dts & tm _
And .Value >= dte & tm _
Then _
ws.Rows(RowToTest).EntireRow.Delete
End WithNext RowToTest
Next
End Sub
[/INDENT]
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!