Why does this keep deleting Row 2?

[JB27]

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

 

[Richard Schollar]

Row 2 is the header row for the autofilter and hence will always be visible.

 

[JB27]

But it isn't staying visible. It is being deleted. Even when I change it to just M: rather than M2: Row 2 is being deleted.

 

[Richard Schollar]

I assume you have headers in row 1? If so, this should be the start of your autofilter range. Try this:

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
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 Sheets("Sheet2")
    .AutoFilterMode = False
    Set rng = .Range("M1:M" & Cells(.Rows.Count, "M").End(xlUp).Row)
    rng.AutoFilter field:=1, Criteria1:="<" & dtFrom
    rng.Offset(1).EntireRow.Delete
    rng.AutoFilter field:=1, Criteria1:=">" & dtTo
    rng.Offset(1).EntireRow.Delete
    .AutoFilterMode = False
End With
End Sub

 

[JB27]

PERFECT! THANKS! I tried doing M:M but that was still deleting row 1 and two. It works now. THANK YOU VERY MUCH! KUDOS TO YOU.