Tests Copying, Pasting, API Cliipboard issues. and Rough notes on Advanced API stuff

**DocAElstein** · 05-26-2020, 02:16 PM

Macro to answer this Thread
https://excelfox.com/forum/showthrea...ete-entire-row

Code:

Sub STEP9t() '  https://excelfox.com/forum/showthread.php/2520-Conditionally-compare-the-data-amp-delete-entire-row
Rem 1 Worksheets info
'1_1 sample1.xls
Dim Wb1 As Workbook, Ws1 As Worksheet
' Set Wb = Workbooks.Open(ThisWorkbook.Path & "\" & "Sample1.xls")
' Set Wb = Workbooks.Open(ThisWorkbook.Path & Application.PathSeparator & "Sample1.xls")
 Set Wb1 = Workbooks("1.xls")
 Set Ws1 = Wb1.Worksheets.Item(1)
Dim arrWs1() As Variant: Let arrWs1() = Ws1.Range("A1").CurrentRegion.Value2
Dim Lr1 As Long: Let Lr1 = UBound(arrWs1(), 1)
Dim arrS1() As Variant 'Let arrS1() = Ws1.Range("A1").CurrentRegion.Value
Let arrS1() = Ws1.Range("A1:J" & Lr1 & "").Value ' Input data range
'1_2 Alert.xls
Dim Wb2 As Workbook, Ws2 As Worksheet
 Set Wb2 = Workbooks("Alert.xls")
 Set Ws2 = Wb2.Worksheets.Item(1)
Dim RwCnt2 As Long: Let RwCnt2 = Ws2.Range("A" & Ws2.Rows.Count & "").End(xlUp).Row
'1_2d) second column in Alert.xls
Dim Clm2() As Variant: Let Clm2() = Ws2.Range("B1:B" & RwCnt2 & "").Value

Rem 3
Dim Cnt As Long, MtchRes As Variant
    For Cnt = UBound(arrS1(), 1) To 2 Step -1 '  "row" count, Cnt
     Select Case arrS1(Cnt, 10) ' column I
      Case "BUY"   'If column J of 1.xls has buy then
        If arrS1(Cnt, 8) < arrS1(Cnt, 4) Then  ' column H of 1.xls is not greater than column D of 1.xls
         Let MtchRes = Application.Match(arrWs1(Cnt, 9), Clm2(), 0) ' match column I data of 1.xls with column B of alert.xls
            If IsError(MtchRes) Then
            ' no match result so do nothing
            Else
             Ws2.Range("A" & MtchRes & ":K" & MtchRes & "").Delete shift:=xlUp   ' delete that entire row of alert.xls
            End If:
        Else
        End If
      Case ""      ' If column J of 1.xls has a blank cell then
       Let MtchRes = Application.Match(arrWs1(Cnt, 9), Clm2(), 0) ' match column I data of 1.xls with column B of alert.xls
        If IsError(MtchRes) Then
        ' no match result so do nothing
        Else
         Ws2.Range("A" & MtchRes & ":K" & MtchRes & "").Delete shift:=xlUp   ' delete that entire row of alert.xls
        End If
      Case "SHORT"  'If column J is SHORT then
        If arrS1(Cnt, 8) > arrS1(Cnt, 4) Then ' column H of 1.xls is Greater than than column D
         Let MtchRes = Application.Match(arrWs1(Cnt, 9), Clm2(), 0) ' match column I data of 1.xls with column B of alert.xls
            If IsError(MtchRes) Then
            ' no match result so do nothing
            Else
             Ws2.Range("A" & MtchRes & ":K" & MtchRes & "").Delete shift:=xlUp   ' delete that entire row of alert.xls
            End If
        Else
        End If
     End Select
    Next Cnt

End Sub