So I am new to using macros and am looking for some help.
I am hoping to do two things.
1) Create a macro that will quickly and automatically delete data that is outside of the date range I am interested in. (I think a date search, then a group delete would be quickest, but am unsure of how to do this)
2) Instead of opening the macro and editing the date range via code, I would prefer a simple userform that uses txt boxes to insert the dates into the code.
Below is the code I have gotten so far. I am currently using a loop function to delete rows outside my date range, but its relatively slow.
Code:
Sub FormatData()
'Arrange Date by Req ID
Rows("1:1").Select
Selection.AutoFilter
Range("A1:X1187").Sort Key1:=Range("X1"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
'Adjust width of Column A to show all of date
Columns("A:A").EntireColumn.AutoFit
End Sub
Range("A:A").NumberFormat = "m/d/yyyy"
Rows(2).Select
ActiveWindow.FreezePanes = True
Rows(1).AutoFilter
'Create a variable that = the last used row in column A
LR = Cells(Rows.Count, "A").End(xlUp).Row
'Create a looping variable # to go backwards from the last used Row# to 1
For i = LR To 2 Step -1
If Cells(i, "A").Value < #9/27/2010 7:00:00 AM# Or Cells(i, "A").Value > #9/28/2010 7:00:00 AM# Then
Rows(i).EntireRow.Delete shift:=xlUp
End If
Next i
End Sub
</PRE>
I am hoping to do two things.
1) Create a macro that will quickly and automatically delete data that is outside of the date range I am interested in. (I think a date search, then a group delete would be quickest, but am unsure of how to do this)
2) Instead of opening the macro and editing the date range via code, I would prefer a simple userform that uses txt boxes to insert the dates into the code.
Below is the code I have gotten so far. I am currently using a loop function to delete rows outside my date range, but its relatively slow.
Code:
Sub FormatData()
'Arrange Date by Req ID
Rows("1:1").Select
Selection.AutoFilter
Range("A1:X1187").Sort Key1:=Range("X1"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
'Adjust width of Column A to show all of date
Columns("A:A").EntireColumn.AutoFit
End Sub
Range("A:A").NumberFormat = "m/d/yyyy"
Rows(2).Select
ActiveWindow.FreezePanes = True
Rows(1).AutoFilter
'Create a variable that = the last used row in column A
LR = Cells(Rows.Count, "A").End(xlUp).Row
'Create a looping variable # to go backwards from the last used Row# to 1
For i = LR To 2 Step -1
If Cells(i, "A").Value < #9/27/2010 7:00:00 AM# Or Cells(i, "A").Value > #9/28/2010 7:00:00 AM# Then
Rows(i).EntireRow.Delete shift:=xlUp
End If
Next i
End Sub
</PRE>
Last edited: