Same results again....
( I assume that STEP1U.xlsb is like previous 1.xlsx or sample1.xlsx
and
I assume 1.xls is like previous sample2.xlsx and 2.xlsx )
Before:-
_____ Workbook: STEP1U.xlsb ( Using Excel 2007 32 bit )
Worksheet: Sheet1
Row\Col ACC DLF
_____ Workbook: 1.xls ( Using Excel 2007 32 bit )
Worksheet: 1-Sheet1
Row\Col Exchange Symbol Series/Expiry Open High Low Prev Close LTP NSE ACC EQ NSE ADANIPOWER EQ NSE DLF EQ NSE AMBUJACEM EQ NSE APOLLOHOSP EQ
Same macro as before ( http://www.excelfox.com/forum/showth...ll=1#post12887 )
Run macro ...
Code:Sub conditionally_delete() ' http://www.excelfox.com/forum/showthread.php/2436-conditionally-delete-entire-row?p=12890&viewfull=1#post12890
Dim Wb1 As Workbook, Wb2 As Workbook, Ws1 As Worksheet, Ws2 As Worksheet
Set Wb1 = Workbooks("STEP1U.xlsb") ' Workbooks("sample1.xlsx") ' Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\Upstox\STEP1U.xlsb")
Set Ws1 = Wb1.Worksheets.Item("Sheet1") ' worksheet with string tab name of Sheet1
Set Wb2 = Workbooks("1.xls") ' Workbooks("sample2.xlsx") ' Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\1.xls")
Set Ws2 = Wb2.Worksheets.Item(1) ' worksheet of first tab
'1b Ranges
Dim Rng1 As Range, Rng2 As Range
Set Rng1 = Ws1.Range("A1").CurrentRegion: Set Rng2 = Ws2.Range("A1").CurrentRegion
Dim Rng1A As Range, Rng2B As Range
Set Rng1A = Rng1.Range("A1:A" & Rng1.Rows.Count & ""): Set Rng2B = Rng2.Range("B1:B" & Rng2.Rows.Count & "") ' : Set Rng2A = Rng2.Range("A1:A" & Rng2.Rows.Count & "")
Rem 2 take each row in column A of 1.xlsx and compare it with each row in column B of of 2.xlsx
Dim Rws As Long
For Rws = Rng1.Rows.Count To 2 Step -1
If Rng1A.Item(Rws).Value = Rng2B.Item(Rws).Value Then
' Do nothing
Else
Rng2B.Item(Rws).EntireRow.Delete Shift:=xlUp
End If
Next Rws
' Wb1.Save
' Wb1.Close
' Wb2.Save
' Wb2.Close
End Sub
After run macro
_____ Workbook: STEP1U.xlsb ( Using Excel 2007 32 bit )
Worksheet: Sheet1
Row\Col ACC DLF
_____ Workbook: 1.xls ( Using Excel 2007 32 bit )
Worksheet: 1-Sheet1
Row\Col Exchange Symbol Series/Expiry Open High Low Prev Close LTP NSE ACC EQ NSE DLF EQ NSE AMBUJACEM EQ NSE APOLLOHOSP EQ
Same results with similar data files.
Third attempt at doing almost the same thing.
No major difference in data or results.
Results are correct, or at least they all do what you asked for....
ACC column A in STEP1U.xlsb was equal to ACC column B in 1.xls then keep that entire row
DLF column A in STEP1U.xlsb was not equal to ADANIPOWER column B in 1.xls , so row 3 in 1.xls , entire row, is deleted.
.........if column A of 1.xlsx matches with column B of 2.xlsx then keep that entire row of 2.xlsx & if not matches then delete the entire row of 2.xlsx......
