Delete rows based on match criteria in two excel files, 1 might be .csv file .Opened in Excel=Fail Chaos

**DocAElstein** · 05-28-2020, 02:56 PM

Sub jindonsTesties()
' PART 1 ================================
This is mainly concerned with the excel file, “1.xls”
Its final purpose, its final result, is a text string , txt, which ADO will recognise to select/ filter a text file based on field information. The second field, F2 , ( which we can approximately regard as column B of alert.csv ) will be used to select the rows we want. Those we don’t take, will be then effectively the deleted records ( rows ) of the text file.

The original given logic of here
https://excelfox.com/forum/showthread.php/2500-Conditionally-delete-entire-row-with-calculation-within-files/page2#post13440
https://excelfox.com/forum/showthrea...age2#post13460

is this
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 second field, F2 ( column B ) of text file , alert.csv and if it matches then delete that entire record (row) of alert.csv
If column J of 1.xls has a blank cell then match column I data of 1.xls with second field ( column B ) of of text file , alert.csv and if it matches then delete that entire record (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 second field, F2 ( column B ) of of text file , alert.csv and if it matches then delete that entire record (row) of alert.csv
We can re write that logic thus:
(If column J of 1.xls has buy & column H of 1.xls is not greater than column D of 1.xls
OR
If column J of 1.xls has short & column H of 1.xls is Greater than column D of 1.xls
OR
If column J of 1.xls has a blank)
Then
then match column I data of 1.xls with second field ( column B ) of of text file , alert.csv and if it matches then delete that entire record (row) of alert.csv

Sub jindonsTesties() is concerned with the excel file, “1.xls”, and solves this part of the re written criteria:
(If column J of 1.xls has buy & column H of 1.xls is not greater than column D of 1.xls
OR
If column J of 1.xls has short & column H of 1.xls is Greater than column D of 1.xls
OR
If column J of 1.xls has a blank)

Finally it produces a string that is in the form of a “SQL” command that is recognised by ADO stuff to select/filter records(rows) from a text file based on values in a field(column). We finally want a text line, txt, like this
__ (Not F2 = 15083) And (Not F2 = 17388) And (Not F2 = 100)

Rem 1 Worksheets info:
This only opens the Excel File, and does the usual worksheet characteristic info…

Rem 2
The purpose of this section is to build 3 1 dimensional arrays, each for the values of I that meet the criteria for being considered further for a match.

This uses worksheet array type formulas within VBA via Evaluate(“ “) to give arrays of the column I values meeting the criteria to be further used in the Match part
finally have a text line, txt, like this
__ (Not F2 = 15083) And (Not F2 = 17388) And (Not F2 = 100)

Code:

'    Alert 29May excelforum..csv         https://www.excelforum.com/excel-programming-vba-macros/1317589-conditionally-compare-the-data-and-delete-entire-row.html
'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

' With Sheets(1)
'           Lr = .Range("a" & Rows.Count).End(xlUp).Row

' Missed 3 dots.
'    With GetObject(fn)
'        With .Sheets(1)
'            Lr = .Range("a" & .Rows.Count).End(xlUp).Row
Sub OpenAlert29Mayexcelforum__csv()
 Workbooks.Open Filename:=ThisWorkbook.Path & "\Alert 29May excelforum..csv"
End Sub

Sub JindonsTesties()  '    Conditionally compare the data & delete entire row - https://www.excelforum.com/excel-programming-vba-macros/1317589-conditionally-compare-the-data-and-delete-entire-row.html#post5340103
' PART 1 ================================
    Dim LR As Long, e, fn As String, myCSV As String, txt As String, vTemp As Variant, arrTemp() As Variant
Rem 1 Workbooks, Worksheets info
'    fn = ThisWorkbook.Path & "\1.xls"                          '"C:\Users\WolfieeeStyle\Desktop\1.xls"
'    myCSV = ThisWorkbook.Path & "\Alert 29May excelforum..csv" ' "C:\Users\WolfieeeStyle\Desktop\Alert..csv"
'    If (Dir(fn) = "") + (Dir(myCSV) = "") Then MsgBox "Invalid file Path/Name": Exit Sub
Dim Wb1 As Workbook
 Set Wb1 = Workbooks("1.xls")                                         '   CHANGE TO SUIT
' Set Wb1 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\1.xls")    '   CHANGE TO SUIT
    'With GetObject(fn)
        'With .Worksheets.Item(1)
Dim Ws1 As Worksheet
 Set Ws1 = Wb1.Worksheets.Item(1)
 Let LR = Ws1.Range("a" & Ws1.Rows.Count).End(xlUp).Row ' 1.xls last row of data
Rem 2 Make 1 Dimensional arrays for values
'2a) If column J of 1.xls has buy & column H of 1.xls is not greater than column D of 1.xls
 'Let vTemp = .Evaluate("transpose(if((j2:j" & LR & "=""buy"")*(h2:h" & LR & "
 Let arrTemp() = Ws1.Evaluate("transpose(if((j2:j" & LR & "=""buy"")*(h2:h" & LR & "For Each e In Filter(arrTemp(), False, 0) ' Filter(arrTemp(), False, 0) is empty
     Let txt = txt & " And (Not F2 = " & e & ")"
    Next
'2b) If column J of 1.xls has short & column H of 1.xls is Greater than  column D of 1.xls
' Let vTemp = .Evaluate("transpose(if((j2:j" & LR & "=""short"")*(h2:h" & LR & ">d2:d" & LR & "),i2:i" & LR & "))")
 Let arrTemp() = Ws1.Evaluate("transpose(if((j2:j" & LR & "=""short"")*(h2:h" & LR & ">d2:d" & LR & "),i2:i" & LR & "))")
    For Each e In Filter(arrTemp(), False, 0) ' Filter(arrTemp(), False, 0) is {100}
     Let txt = txt & " And (Not F2 = " & e & ")"
    Next
'2c) If column J of 1.xls has a blank
' Let vTemp = .Evaluate("transpose(if(j2:j" & LR & "="""",i2:i" & LR & "))")
 Let arrTemp() = Ws1.Evaluate("transpose(if(j2:j" & LR & "="""",i2:i" & LR & "))")
    For Each e In Filter(arrTemp(), False, 0) '  Filter(arrTemp(), False, 0) is {15083, 17388}
     Let txt = txt & " And (Not F2 = " & e & ")"
    Next
        'End With ' final txt is   And (Not F2 = 15083) And (Not F2 = 17388) And (Not F2 = 100)
        '.Close
    'End With
'    CreateNew myCSV, Mid$(txt, 5)
' Let txt = Mid$(txt, 6) ' take off the first  " AND "

' Part 2 ===============================================================================
'End Sub
'Sub MyTests_CreateNew()