Appendix Thread. ( Codes for other Threads, ( Avinash ).)

**DocAElstein** · 09-04-2019, 03:03 PM

Using data from last post

Before=
_____ Workbook: 1.xls ( Using Excel 2007 32 bit )

_Row\^Col	A	B	C	D	E	F	G	H
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

Worksheet: 1-Sheet1

After=
_____ Workbook: 1.xls ( Using Excel 2007 32 bit )

_Row\^Col	A	B	C	D	E	F	G	H
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

Worksheet: 1-Sheet1

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