Hi Everyone,
Could you please help me with the macro. I have a macro which converts the dates (Maturity date column in attached excel sheet) into text to columns and format it to
Format cells --> Date-->14-mar-2001 which is working fine.
Now i want the macro to delete entire columns of all rows which are greater than the variable date given in "Z". I have tried a macro but it is not working. It is taking ages to find the works or it is not correctly recognizing the dates.
Here is my code till now and attached is the data file.
It is fine if we can work it out with Auto filters or delete data row by row.
Please help. Thanks in advance.
Code:
Sub Macro1()
Columns("M").Select
Selection.TextToColumns Destination:=Range("M1"), DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, Tab:=True, FieldInfo:=Array(1, 4), TrailingMinusNumbers:=True
Selection.NumberFormat = "[$-409]d-mmm-yyyy;@"
Dim x As Date
Dim LR As Long
x = InputBox("Enter the from date in format MM/DD/YYYY")
LR = Range("C" & Rows.Count).End(xlUp).Row
For i = LR To 2 Step -1
If Cells(i, 13).Value > x Then
Cells(i, 13).EntireRow.Delete
End If
Next i
End Sub