How to use macro filter excel?

I want to filter date in excel in sheet CaseCA and sheet CaseCH.
input.xlsx (11.1 KB)
output.xlsx (10.8 KB)

I have variable for dateCasaCA as string = 01/01/2021
and variable for dateCasaCH as string = 02/05/2021

Condition : I want filter date in sheet CasaCA by date < dateCasaCA (01/01/2021)
and filter date in sheet CasaCH by date < dateCasaCH (02/05/2021) and write it in same file.

Please guide me about it.

@fairymemay - Are you specifically looking for macro ?? we can easily use LINQ query to filter the result and write to the output sheet.

@prasath17 I’m ok for use LINQ query.
But I want to write in same file and same sheet.

Please guide me.

@fairymemay - may I ask why? Since you have 5 rows in the input sheet and 2 in the output for CaseCA if you write the output to the same sheet your other rows will still be there I am afraid. So I won’t write it to the same sheet.

@prasath17 If clear data from sheet CaseCA and CashCH before write.
Is it possible or not?

@fairymemay - That is equivalent to writing the result to new sheet or the new workbook right???

@prasath17 Ok , If can’t write in same sheet.
Can write in new sheet but same name (delete original sheet) ?

You can’t do that…May be first we can delete the sheet and create a new sheet with same name…

@prasath17 Delete the sheet and create a new sheet with same name // okay

Please solution for filter excel to me.

@fairymemay - Here you go (69.2 KB)

Please remove the “input_Org.xlsx” from the folder and then run the workflow you will see the output gets created.

date in sheet CasaCH by date < dateCasaCH (02/05/2021) → For this case, only one date qualified because your date format in the sheet is dd/MM/yyyy.

Hope this helps…

1 Like

@fairymemay - Did you get a chance to try this? If yes and solved your query , please mark my post as solution.

1 Like

@prasath17 If I use my excel , it show error as below.

File excel : input.xlsx (15.5 KB) ( I delete sensitive date in my file)

I edit filter as below.


Please guide me more.

Whatever the sheet you initially shared, i have provided the fully working code…

Recently shraed one is not in proper date format. Check the cell type on top it is “General”

This is our original format

@prasath17 If​ I​ want​ use​ format​ General.
How to solve. XAML?

I can help. But May I know why your original requirement has changed?

1 Like

@prasath17 Now, I​ download​ file​ from​ internal​ system.​----->due​ date​ format​ type​ General.
But original​ file​ that​ example​ data

@prasath17 If you have free time Please advise for​ solve this case.

Thank​ you.

@fairymemay - please let me know what is the exact filtering requirement?

1 Like

@prasath17 I have variable date1 as sting = 20/05/2021

I want filter sheet caseCA column Due Date (format type General) < date1 and write in same sheet.
but if column Due Date not have data < date1 , I want to show header in sheet only.

input.xlsx (20.9 KB)

Please suggest​ solution​ for​ this​ case.

1 Like

@fairymemay - I will work on this today and let you know.

1 Like