Why does this keep deleting Row 2?

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



 

Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

JB27

New Member
Joined
Oct 2, 2009
Messages
41
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

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
I assume you have headers in row 1? If so, this should be the start of your autofilter range. Try this:

Code:
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

New Member
Joined
Oct 2, 2009
Messages
41
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,509
Messages
5,596,558
Members
414,077
Latest member
ammylar5

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top