Hi Frineds,
My macro is not working, i need to apply filter thur message box and delete after the given date rows.I have attached the macro sheet for your reference .
Kindly help to correct the same.
Regards,
Prabhu
Hi Frineds,
My macro is not working, i need to apply filter thur message box and delete after the given date rows.I have attached the macro sheet for your reference .
Kindly help to correct the same.
Regards,
Prabhu
Try this:
Code:Sub MYFILTER() Dim W1Startdate As Date W1Startdate = InputBox("Please Enter the Start Data in DD/MM/YYYY format") range("A2:T" & range("A1").End(xlDown).Row).AutoFilter Field:=7, Criteria1:=">" & W1Startdate 'range("A4:T" & range("A2").End(xlDown).Row).SpecialCells(12).EntireRow.Delete End Sub
Alan
Did you debug your code first? http://www.cpearson.com/excel/DebuggingVBA.aspx
Like my answer? Click * below
Database Normalization
Database Principles
Pivot Table Tips
Excel Video Tutorials
SumProduct Video Tutorial
DataPig Access Tutorials
MS Query Tutorial
SQL Tutorial
Hi,
I tried, but it is not deleting the record after the given date. i.e it is struck on the filter.
Kindly helpCode:Sub MYFILTER() Dim W1Startdate As Date W1Startdate = InputBox("Please Enter the Start Data in DD/MM/YYYY format") range("A2:T" & range("A1").End(xlDown).Row).AutoFilter Field:=7, Criteria1:=">" & W1Startdate range("A3:T" & range("A2").End(xlDown).Row).SpecialCells(12).EntireRow.Delete End Sub
Last edited by Excel Fox; 11-01-2014 at 09:16 PM. Reason: Code Tag added
Try this:
Code:Option Explicit Sub NewDelete() Dim W1Startdate As Date W1Startdate = InputBox("Please Enter the Start Data in DD/MM/YYYY format") Dim lr As Long lr = Range("G" & Rows.Count).End(xlUp).Row Dim i As Long Application.ScreenUpdating = False For i = lr To 3 Step -1 If Range("G" & i) >= W1Startdate Then Range("G" & i).EntireRow.Delete End If Next i Application.ScreenUpdating = True MsgBox "task complete" End Sub
Please use code tags when posting VBA Code in the forum. It makes for easier reading and is a forum standard.
Alan
Did you debug your code first? http://www.cpearson.com/excel/DebuggingVBA.aspx
Like my answer? Click * below
Database Normalization
Database Principles
Pivot Table Tips
Excel Video Tutorials
SumProduct Video Tutorial
DataPig Access Tutorials
MS Query Tutorial
SQL Tutorial
Thanks it is working!
But it is taking huge amount of time when we delete larger data. i think it is deleting row by row.
can we make change to delete the enter in one short now like deleting manually?
Hi
Try
Code:Sub MYFILTER() Dim W1Startdate As String Dim dtFilter As Date W1Startdate = Application.InputBox("Please Enter the Start Data in DD/MM/YYYY format", Type:=2) If W1Startdate = "False" Then Exit Sub If IsNumeric(Replace(W1Startdate, "/", vbNullString)) Then dtFilter = DateSerial(Split(W1Startdate, "/")(2), Split(W1Startdate, "/")(1), Split(W1Startdate, "/")(0)) Range("A2:T" & Range("A1").End(xlDown).Row).AutoFilter Field:=7, Criteria1:=">" & dtFilter Range("A3:T" & Range("A2").End(xlDown).Row).SpecialCells(12).EntireRow.Delete If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilterMode = False End If End Sub
Cheers !
Excel Range to BBCode Table
Use Social Networking Tools If You Like the Answers !
Message to Cross Posters
@ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)
No Sir,
It is not deleting.
Any error ? how many rows of data you have ?
Cheers !
Excel Range to BBCode Table
Use Social Networking Tools If You Like the Answers !
Message to Cross Posters
@ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)
I found 2 issues:
1.It is taking MM/DD/YYYY instead of DD/MM/YYYY.
2. If the input dates between 1- 10 example 08/10/2014(10th Aug 2014) it is deleting correctly but the input dates between 11-31 example 08/15/2014 it is not taking any action.
I have attached the data for your reference. Kindly help to correct the same.
Regards,
Prabhu
It's working here. You need to enter the date in dd/mm/yyyy format.
Cheers !
Excel Range to BBCode Table
Use Social Networking Tools If You Like the Answers !
Message to Cross Posters
@ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)
Bookmarks