Using data from last post
Before=
_____ Workbook: 1.xls ( Using Excel 2007 32 bit )
| Row\Col |
A |
B |
C |
D |
E |
F |
G |
H |
I |
|---|
1 |
Exchange |
Symbol |
Series/Expiry |
Open |
High |
Low |
Prev Close |
LTP |
|
2 |
NSE |
ADANIPORTS |
EQ |
409 |
409 |
398.65 |
407.2 |
402 |
|
3 |
NSE |
ABC |
EQ |
216.2 |
219.15 |
215.15 |
215.8 |
218 |
|
4 |
NSE |
ASIANPAINT |
EQ |
1409 |
1441.95 |
1401.85 |
1404.2 |
1441.3 |
|
5 |
NSE |
AXISBANK |
EQ |
732.9 |
739.3 |
728.15 |
727.45 |
733.65 |
|
6 |
NSE |
BANKBARODA |
EQ |
118.8 |
119.15 |
114.7 |
118.35 |
115.25 |
|
7 |
NSE |
BHARTIARTL |
EQ |
342.95 |
348.5 |
337.4 |
342.55 |
343.05 |
|
8 |
|
|
|
|
|
|
|
|
|
After=
_____ Workbook: 1.xls ( Using Excel 2007 32 bit )
| Row\Col |
A |
B |
C |
D |
E |
F |
G |
H |
I |
|---|
1 |
Exchange |
Symbol |
Series/Expiry |
Open |
High |
Low |
Prev Close |
LTP |
|
2 |
NSE |
ABC |
EQ |
216.2 |
219.15 |
215.15 |
215.8 |
218 |
|
3 |
NSE |
BHARTIARTL |
EQ |
342.95 |
348.5 |
337.4 |
342.55 |
343.05 |
|
4 |
|
|
|
|
|
|
|
|
|
Code:
'
Sub Vixer7() ' http://www.excelfox.com/forum/showthread.php/2364-Delete-row
Rem 1 Workbook and worksheets info
'1a) Workbook info
Dim Wbm As Workbook: Set Wbm = ThisWorkbook ' The workbook containing macro
Dim Wb1 As Workbook, Wb2 As Workbook ' (These will be set later when the workbooks are opened)
Dim strWb1 As String: Let strWb1 = "1.xls"
Dim strWb2 As String: Let strWb2 = "BasketOrder.xlsx" ' "BasketOrder..xlsx"
'1b) Worksheets info
Dim Ws1 As Worksheet, Ws2 As Worksheet ' (These will be set later when the workbooks are opened)
Dim Lr1 As Long, Lr2 As Long: Let Lr1 = 7: Lr2 = 6 ' For sample file
Rem 2 Open files ..... we have to Open all the files all files are closed except the vba placed file
' Workbooks.Open Filename:="F:\Excel0202015Jan2016\ExcelFox\vixer\BasketOrder..xlsx"
Workbooks.Open Filename:=ThisWorkbook.Path & "\" & strWb2
Set Wb2 = ActiveWorkbook '
Set Ws2 = Wb2.Worksheets.Item(1)
' Workbooks.Open Filename:="F:\Excel0202015Jan2016\ExcelFox\vixer\1.xls"
Workbooks.Open Filename:=ThisWorkbook.Path & "\" & strWb1
Set Wb1 = ActiveWorkbook ' The workbook just opened will now be the current active workbook
Set Ws1 = Wb1.Worksheets.Item(1)
Rem 3 The Process ..."....If cells of column C of basketorder.xlsx matches with cells of column B of 1.xlsx then delete the entire row of 1.xlsx...."....
' 3a) Range.Find Method range info
' 3a)(i) Search range ( range to be searched )
Dim rngSrch As Range: Set rngSrch = Ws2.Range("C1:C" & Lr2 & "") ' .."....column C of basketorder.xlsx
' 3a)(ii)' Data range, items to be searched for
Dim rngDta As Range: Set rngDta = Ws1.Range("B2:B" & Lr1 & "") ' .."....cells of column B of 1.xlsx
' 3b) MAIN LOOP for all cells in basketorder.xlsx
Dim Cnt As Long '_====================================MAIN LOOP===========================================
For Cnt = Lr2 To 1 Step -1 ' data range to be searched for.... Important: I am going to delete rows in a loop: usually do such delete things in a backward loop. This is because I then effectively do a process on a cell or cells "behind me". So the process is done on a cell or cells no longer being considered. If I do the looping conventionally in the forward direction, then modification caused by the delete may effect the cells above, particularly their position. This can cause problems: After a delete, the cells above "move down". On the next loop I will then consider a cell above where I just was. So I will likely miss the next row to be considered, since that now occupies the position of the current loop. An alternative would be to loop forward, but after a delete to reduce the Loop count, Cnt, by 1. But changing the loop count variable in a loop is generally considered to be a bad idea https://www.mrexcel.com/forum/excel-questions/803662-byte-backward-loop.html#post3929967
Dim MtchedCel As Variant ' For the range object of a matched cell if found, if not found it will be Nothing , so we must use a variant to allow for the type of Range or Nothing
Set MtchedCel = rngSrch.Find(What:=rngDta.Item(Cnt), After:=rngSrch.Item(1), LookIn:=xlValues, Lookat:=xlWhole, Searchdirection:=xlNext, MatchCase:=True) ' rngDta.Item(Cnt) will be a cell of column C of basketorder.xlsx
If Not MtchedCel Is Nothing Then ' If cell of column C of basketorder.xlsx matches with cells of column B of 1.xlsx Then .....
rngDta.Rows(Cnt).EntireRow.Delete Shift:=xlUp ' ..... delete the entire row of 1.xlsx
Else
End If
Next Cnt '_====================================MAIN LOOP===============================================
Rem 4 ...."... after the process close and save the file so that changes should be saved
Wb1.Close savechanges:=True
End Sub
Reply With Quote
Originally Posted by sumanjjj
Bookmarks