Conditionally compare the data & delete entire row

[zyxw1234]

If column J of 1.xls has buy & column H of 1.xls is not greater than column D of 1.xls then match column I data of 1.xls with column B of alert.csv and if it matches then delete that entire row of alert.csv
If column J of 1.xls has a blank cell then match column I data of 1.xls with column B of alert.csv and if it matches then delete that entire row of alert.csv
If column J of 1.xls has short & column H of 1.xls is Greater than than column D of 1.xls then match column I data of 1.xls with column B of alert.csv and if it matches then delete that entire row of alert.csv



These are the three condition that macro will follow and based on that it will work
all files are located in different path, macro will be placed in separte file macro.xlsm
sheet name can be anything





File Link
https://drive.google.com/open?id=1Zi_g6 ... gVh1r3_wkh_

https://drive.google.com/open?id=1TgW58 ... n_S-p7r3l5

[HansV]

The second link is not valid.

[zyxw1234]

I corrected it plz recheck it

[HansV]

Nope.

You can zip the .csv file and attach the zip file to your reply here.

[zyxw1234]

https://drive.google.com/open?id=1FGmcj ... _G_LV2H85b


I zipped the file plz see

[HansV]

Please start writing the macro, and come back if you have a specific question.

[zyxw1234]

I was writing the same, but this is something difficult & i was unable to wirte it that's why i needed help for the same

[HansV]

We cannot provide complete solutions for you.

[zyxw1234]

No problem Thnx Alot for giving ur precious time HansV Sir

[Leif]

I was writing the same, but this is something difficult & i was unable to wirte it that's why i needed help for the same

You are not asking for "help", you are asking for someone to provide the solution. If you were asking for help, you would at least put some effort into learning VBA, which so far, you have clearly failed to do.

Perhaps you should try asking at Chandoo.org Excel Forums once more - you are pushing the limits of what you can get away with here. Again.

[zyxw1234]

Bro u know this https://chandoo.org/forum/threads/vba-macro.44302/
Great but that problem is not solved yet
If u have any suggestion or solution regarding that question then plz provide it will be a great help
That problem is not solved yet & this problem will be solved, i am working on it

[Leif]

[HansV]

Also see Conditionally delete entire row with calculation within files on ExcelFox.

[zyxw1234]

The output is same but the process is different in both the post
Sometimes misunderstanding happens But i have no issues with that i got banned on that forum so i asked here in this forum

Now after googling,I made the code but little modification is there

Code: Select all

Sub test()
    Dim LR As Long, e, fn As String, myCSV As String, txt As String
    fn = "C:UsersWolfieeeStyleDesktop1.xls"
    myCSV = "C:UsersWolfieeeStyleDesktopAlert..csv"
    If (Dir(fn) = "") + (Dir(myCSV) = "") Then MsgBox "Invalid file Path/Name": Exit Sub
     With GetObject(fn)
        With .Sheets(1)
            LR = .Range("a" & .Rows.Count).End(xlUp).Row
            For Each e In Filter(.Evaluate("transpose(if((j2:j" & LR & "=""buy"")*(h2:h" & _
                            LR & "d2:d" & LR & "),i2:i" & LR & "))"), False, 0)
                txt = txt & " And (Not F2 = " & e & ")"
            Next
            For Each e In Filter(.Evaluate("transpose(if(j2:j" & LR & "="""",i2:i" & LR & "))"), False, 0)
                txt = txt & " And (Not F2 = " & e & ")"
            Next
        End With
        .Close
    End With
    CreateNew myCSV, Mid$(txt, 5)
End Sub

Private Sub CreateNew(myCSV As String, txt As String)
    Dim fn As String, cn As Object, rs As Object, x
    fn = Left$(myCSV, InStrRev(myCSV, "")) & "temp.csv"
    FileCopy myCSV, fn
    Set cn = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")
    With cn
        .Provider = "Microsoft.Ace.OLEDB.12.0"
        .Properties("Extended Properties") = "Text;HDR=No;"
        .Open Left(fn, InStrRev(fn, ""))
    End With
    rs.Open "Select * From [temp.csv] Where " & txt, cn, 3
    x = rs.GetString(, , ",", vbCrLf)
    Set cn = Nothing: Set rs = Nothing
    Kill fn
    Open Replace(myCSV, ".csv", "_Filtered.csv") For Output As #1
        Print #1, x;
    Close #1
End Sub

This code gives me the correct output, But it creates a new file & in that it gives me the correct output with different file name
So what i wanted in this macro is mentioned below
After doing the complete process at the end it should Kill "C:UsersWolfieeeStyleDesktopAlert..csv" & rename the file name as alert..csv
Only two things i need so plz have a look sir and help me out in solving this problem Sir

[HansV]

You already know how to delete the original Alert..csv
To rename a file, use

Name oldname As newname

where oldname is the path and filename of the file that has been created, and newname is the path and filename that you want

[zyxw1234]

Code: Select all

Sub test()
    Dim LR As Long, e, fn As String, myCSV As String, txt As String
    fn = "C:UsersWolfieeeStyleDesktop1.xls"
    myCSV = "C:UsersWolfieeeStyleDesktopAlert..csv"
    If (Dir(fn) = "") + (Dir(myCSV) = "") Then MsgBox "Invalid file Path/Name": Exit Sub
     With GetObject(fn)
        With .Sheets(1)
            LR = .Range("a" & .Rows.Count).End(xlUp).Row
            For Each e In Filter(.Evaluate("transpose(if((j2:j" & LR & "=""buy"")*(h2:h" & _
                            LR & "d2:d" & LR & "),i2:i" & LR & "))"), False, 0)
                txt = txt & " And (Not F2 = " & e & ")"
            Next
            For Each e In Filter(.Evaluate("transpose(if(j2:j" & LR & "="""",i2:i" & LR & "))"), False, 0)
                txt = txt & " And (Not F2 = " & e & ")"
            Next
        End With
        .Close
    End With
    CreateNew myCSV, Mid$(txt, 5)
End Sub

Private Sub CreateNew(myCSV As String, txt As String)
    Dim fn As String, cn As Object, rs As Object, x
    fn = Left$(myCSV, InStrRev(myCSV, "")) & "temp.csv"
    FileCopy myCSV, fn
    Set cn = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")
    With cn
        .Provider = "Microsoft.Ace.OLEDB.12.0"
        .Properties("Extended Properties") = "Text;HDR=No;"
        .Open Left(fn, InStrRev(fn, ""))
    End With
    rs.Open "Select * From [temp.csv] Where " & txt, cn, 3
    x = rs.GetString(, , ",", vbCrLf)
    Set cn = Nothing: Set rs = Nothing
    Kill fn
    Open Replace(myCSV, ".csv", "..csv") For Output As #1
        Print #1, x;
    Close #1
Kill "C:UsersWolfieeeStyleDesktopAlert..csv"
Name "C:UsersWolfieeeStyleDesktopAlert...csv" As "C:UsersWolfieeeStyleDesktopAlert..csv"
End Sub

I used this any changes then plz let me know Sir
Thnx Alot for helping me in solving this problem HansV Sir

[HansV]

You should put back the backslashes \ into the paths.

[zyxw1234]

I have used backslash but while putting in the post, it disappears
Problem Solved
Thnx Alot HansV Sir