JB27
New Member
- Joined
- Oct 2, 2009
- Messages
- 41
When I import the sheet, the data in row 2 is there, but as soon as I run the ERASE BY DATE RANGE macro below, it deletes row 2. The problem is that based on the date, Row 2 should NOT be deleted. If I change the part highlighted below in blue to an M3 or other value, I notice it is then deleting that row, regardless of what I enter in for the date range. Any suggestions?
Sub RunMacro()
'Messege
MsgBox "Please follow the on screen prompts. They will show you a value that needs to be entered in the text box. Type the appropriate value and click ok."
'delete by date range MDR
Dim dtFrom As Date, dtTo As Date, rng As Range, Rng2 As Range
Set rng = Sheets("Sheet2").Range("M2:M65536").SpecialCells(xlCellTypeVisible)
dtFrom = CDate(Application.InputBox(prompt:="Enter Start Date (MM/DD/YYYY)", Title:="Dates To Retain", Type:=1))
dtTo = CDate(Application.InputBox(prompt:="Enter End Date (MM/DD/YYYY)", Title:="Dates To Retain", Type:=1))
With rng
.AutoFilter
.AutoFilter Field:=1, Criteria1:="<" & dtFrom
Set rng = Sheets("Sheet2").Range("M2:M65536").SpecialCells(xlCellTypeVisible)
rng.EntireRow.Delete
.AutoFilter Field:=1, Criteria1:=""
.AutoFilter Field:=1, Criteria1:=">" & dtTo
Set rng = Sheets("Sheet2").Range("M2:M65536").SpecialCells(xlCellTypeVisible)
rng.EntireRow.Delete
ActiveSheet.AutoFilterMode = False
End With
Cells.Select
Sub RunMacro()
'Messege
MsgBox "Please follow the on screen prompts. They will show you a value that needs to be entered in the text box. Type the appropriate value and click ok."
'delete by date range MDR
Dim dtFrom As Date, dtTo As Date, rng As Range, Rng2 As Range
Set rng = Sheets("Sheet2").Range("M2:M65536").SpecialCells(xlCellTypeVisible)
dtFrom = CDate(Application.InputBox(prompt:="Enter Start Date (MM/DD/YYYY)", Title:="Dates To Retain", Type:=1))
dtTo = CDate(Application.InputBox(prompt:="Enter End Date (MM/DD/YYYY)", Title:="Dates To Retain", Type:=1))
With rng
.AutoFilter
.AutoFilter Field:=1, Criteria1:="<" & dtFrom
Set rng = Sheets("Sheet2").Range("M2:M65536").SpecialCells(xlCellTypeVisible)
rng.EntireRow.Delete
.AutoFilter Field:=1, Criteria1:=""
.AutoFilter Field:=1, Criteria1:=">" & dtTo
Set rng = Sheets("Sheet2").Range("M2:M65536").SpecialCells(xlCellTypeVisible)
rng.EntireRow.Delete
ActiveSheet.AutoFilterMode = False
End With
Cells.Select