Delete rows based on match criteria in two excel files or single Excel File

**fixer** · 03-23-2020, 05:10 PM

Code:

Sub conditionally_delete()
On Error Resume Next
Dim Wb1 As Workbook, Wb2 As Workbook, Ws1 As Worksheet, Ws2 As Worksheet
 Set Wb1 = 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.Open("C:\Users\WolfieeeStyle\Desktop\1.xls")
 Set Ws2 = Wb2.Worksheets.Item(1) ' worksheet of first tab
'1b Ranges
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

I am getting error with this line Doc Sir

Code:

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 & "")

plz have a relook Doc Sir

**DocAElstein** · 03-23-2020, 05:21 PM

You should be able to see a possible problem ...
Where is your Rng1 and Rng1 defined????
You must have Rng1 and Rng1...
You are missing this

Code:

Dim Rng1 As Range, Rng2 As Range
 Set Rng1 = Ws1.Range("A1").CurrentRegion: Set Rng2 = Ws2.Range("A1").CurrentRegion

Missing Rng1 Rng2.JPG : https://imgur.com/pF9JLdC
Missing Rng1 Rng2.JPG

Code:

'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 ' , Rng2A 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 ..............................

( Note also : It is very bad programming to use On Error Resume Next at the start of a program )

**fixer** · 03-23-2020, 05:51 PM

May i know the reason why it is bad Doc sir So that i can be safe with my work
I have not use this in every code but Now some modification is needed and as per requirement that line is needed but may i know the information Doc sir why i should not use that & what that line will do, it will only stop the Error msg only correct me doc Sir if i am wrong

**DocAElstein** · 03-23-2020, 08:40 PM

On Error Resume Next is one of a few ways to do “Error handling in VBA” ( http://www.excelfox.com/forum/showth...GoRoT-N0Nula-1 )

On Error Resume Next at the start of your program will tell VBA to keep going even if it crashes or has problems many times.
It is like you go on a long journey in your car. But you decide to drive very fast and if you have lots of crashes and accidents you just keep going very fast and ignore all your accidents and crashes.
This can do damage and break things.

It does not just stop the error message. It does stop the error message , you are correct , … but also it does keep going even if things are broken or not working properly. This might do bad damage. You may not notice this damage until something else goes badly wrong. It may cause lots of confusing problems later.

On Error Resume Next at the start of your program is dangerous. You may be lucky one day and it will cause no problems. Or you may be unlucky and it will cause lots of problems and cause lots of damage.

Usually there is a better way to achieve what you want without Error Handling.

Some ways to use Error handling are not too bad.
For example:

This is very Bad

Code:

Sub BadErrorHandling()
On Error Resume Next ' Error handling is ON
Dim Nmbr As Long: Let Nmbr = 0

'
'  code
''  code
''  code
''  code
''  code
''  code
''  code
''  code
''  code
'

 Let Nmbr = 1 / Nmbr

 '
 '
 '  code
''  code
''  code
''  code
''  code
''  code
'
End Sub

This is not so bad

Code:

Sub NotSoBadErrorHandling()

Dim Nmbr As Long: Let Nmbr = 0

'
'  code
''  code
''  code
''  code
''  code
''  code
''  code
''  code
''  code
'
On Error Resume Next ' Error handling is ON
 Let Nmbr = 1 / Nmbr
On Error GoTo 0      ' Error handling is OFF
 '
 '
 '  code
''  code
''  code
''  code
''  code
''  code
'
End Sub

The last macro is not so bad because you only use the error handling for a short piece of the code which you think may error.

But it is still better to do it without error handling

Code:

Sub AlternativeToErrorHandling()

Dim Nmbr As Long: Let Nmbr = 0

'
'  code
''  code
''  code
''  code
''  code
''  code
''  code
''  code
''  code
'

    If Nmbr <> 0 Then Let Nmbr = 1 / Nmbr

 '
 '
 '  code
''  code
''  code
''  code
''  code
''  code
'
End Sub

Alan

**fixer** · 03-23-2020, 08:41 PM

Code:

Sub conditionally_delete()
Dim Wb1 As Workbook, Wb2 As Workbook, Ws1 As Worksheet, Ws2 As Worksheet
 Set Wb1 = 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.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

Doc Sir i used this code but i am not getting desired result i think i have putted something incorrect plz have a look Doc Sir

**fixer** · 03-23-2020, 08:45 PM

No issue Doc Sir i have another plan for this i will not use that line Thnx for the guidance Sir but i am not getting the desired result plz look Sir

**DocAElstein** · 03-23-2020, 08:53 PM

The last macro of yours ( http://www.excelfox.com/forum/showth...ll=1#post12882 ) looks OK

So do two things

_1) Upload a small sample data of
STEP1U.xlsb
And
1.xls

and

_2) tell me what is not doing what you want: Tell me what result is wrong

**fixer** · 03-23-2020, 10:36 PM

plz see the sample file Doc Sir i tried to explain the condition in the sample file doc sir

vba will be placed in a seperate file

**DocAElstein** · 03-23-2020, 11:37 PM

Before:- ( from your uploaded files )
_____ Workbook: sample1.xlsx ( Using Excel 2007 32 bit )

_Row\^Col	A	B	C
1
2	ACC	150
3	DLF	120
4
5			match column A of sample1.xlsx with column B of sample2.xlsx
6			If it matches then do nohing
7			if it doesn’t matches then delete that enire row
8

Worksheet: Sheet1

_____ Workbook: sample2.xlsx ( 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	ACC	EQ	1034	1068.75	988	998.45	998.45
3	NSE	ADANIENT	EQ	123.05	135	118.2	120.9	120.9	This entire row will be deleted
4	NSE	DLF	EQ	235	235.8	203	207.8	207.8
5

Worksheet: Sheet1

Now I run this

Code:

Sub conditionally_delete()
Dim Wb1 As Workbook, Wb2 As Workbook, Ws1 As Worksheet, Ws2 As Worksheet
 Set Wb1 = 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("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:-

_____ Workbook: sample1.xlsx ( Using Excel 2007 32 bit )

_Row\^Col	A	B
1
2	ACC	150
3	DLF	120
4

Worksheet: Sheet1

_____ Workbook: sample2.xlsx ( 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	ACC	EQ	1034	1068.75	988	998.45	998.45
3	NSE	DLF	EQ	235	235.8	203	207.8	207.8
4
5

Worksheet: Sheet1

I see no problem...
This is almost a repeat of previous posts , with similar data files and results ... nothing much new here...