-
3 Attachment(s)
I would like to notify something about this macro and about this post
in this macro
Code:
Let Lr2 = Ws2.Range("A" & Ws2.Rows.Count & "").End(xlUp).Row ' it will be ws1, not ws2 bcoz by using ws1 i am getting correct output
plz have a look to the sample file & macro too...
Problem is Solved according to me But any more suugesstion is welcome
-
Hi
You change may work sometimes, but sometimes with different data it might not work.
In any case it is a stupid thing to do….
Let me explain…
It should be very, very simple to understand:
Consider your uploaded files ….
The range that you search in is in Ws2 (H2(SAMPLE).xlsb ) . Your data goes up to row 36
36 comes from
Let Lr2 = Ws2.Range("A" & Ws2.Rows.Count & "").End(xlUp).Row
You take each data from Ws1 (1(sample) ) and look in that range for a match. You have data in Ws1 up to 142
142 comes from
Let Lr1 = Ws1.Range("A" & Ws1.Rows.Count & "").End(xlUp).Row
That above is all correct. It is as it should be.
If you use
Let Lr2 = Ws1.Range("A" & Ws1.Rows.Count & "").End(xlUp).Row
then you search in the range up to 142 in Ws2
As long as Lr1 is larger than Lr2 then it may work. But your macro may work slower, because you are searching in extra cells that are empty. You do not need to search in empty rows
If Lr1 is less than Lr2, then your macro may not work.
Let me try to explain again , with example of smaller data :
Lets say you have 3 lines of data ( 4 rows ) in Ws2, and 10 rows in Ws1
If you use Lr2 = Ws2.Range("A" & Ws2.Rows.Count & "").End(xlUp).Row , then you will search in this range
_____ Workbook: H2(SAMPLE).xlsb ( Using Excel 2007 32 bit )
| Row\Col |
A |
|---|
| 2 |
ADANIPOWER |
| 3 |
AMARAJABAT |
| 4 |
ASIANPAINT |
If you use this Lr2 = Ws1.Range("A" & Ws1.Rows.Count & "").End(xlUp).Row , then you will search in the range
_____ Workbook: H2(SAMPLE).xlsb ( Using Excel 2007 32 bit )
| Row\Col |
A |
|---|
| 2 |
ADANIPOWER |
| 3 |
AMARAJABAT |
| 4 |
ASIANPAINT |
| 5 |
|
| 6 |
|
| 7 |
|
| 8 |
|
| 9 |
|
| 10 |
|
So there will be no problems , but you are searching over a greater range than you need. So this might make your macro run slower, since you search more rows than you need to.
But now consider another example : Consider that your data in Ws2 has 7 rows, but there are only 3 rows of data in Ws1
If you use this Lr2 = Ws2.Range("A" & Ws2.Rows.Count & "").End(xlUp).Row , then you will search in this range
_____ Workbook: H2(SAMPLE).xlsb ( Using Excel 2007 32 bit )
| Row\Col |
A |
|---|
| 2 |
ADANIPOWER |
| 3 |
AMARAJABAT |
| 4 |
ASIANPAINT |
| 5 |
AMBUJACEM |
| 6 |
APOLLOHOSP |
| 7 |
APOLLOPIPE |
So you will search as you should in all values in Ws2
But if you use this Lr2 = Ws1.Range("A" & Ws1.Rows.Count & "").End(xlUp).Row , then you will be searching over this reduced range:
_____ Workbook: H2(SAMPLE).xlsb ( Using Excel 2007 32 bit )
| Row\Col |
A |
|---|
| 2 |
ADANIPOWER |
| 3 |
AMARAJABAT |
So you are not searching in all the values in Ws2, so you may get the wrong results!
You may choose to use Ws1 instead of Ws2. It is your choice. This will help you Fail in the future when sometimes later you will get the wrong results:
It may fail sometimes if there are more rows of data in Ws2 then in Ws1.
If it does not fail, the macro may work slower then it needs to , because it is searching in cells that it does not need to.
Alan
-
Plz run this macro Doc sir & Plz see the output
This is the updated macro which u have provided Doc Sir & i sent u the sample file today plz run & see the output u will understand (This macro is not giving me the desired output)
Code:
Sub STEP3() ' https://excelfox.com/forum/showthread.php/2364-Delete-rows-based-on-match-criteria-in-two-excel-files?p=14587&viewfull=1#post14587 https://eileenslounge.com/viewtopic.php?f=30&t=34937
Dim Wb1 As Workbook, Wb2 As Workbook
Set Wb1 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\1.xls")
Set Wb2 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\Hot Stocks\H2.xlsb")
Dim Ws1 As Worksheet, Ws2 As Worksheet
Set Ws1 = Wb1.Worksheets.Item(1) ' First worksheet tab counting from the left
Set Ws2 = Wb2.Worksheets.Item(2) ' Second worksheet tab cunting from the left
Dim Lr1 As Long, Lr2 As Long
Let Lr1 = Ws1.Range("A" & Ws1.Rows.Count & "").End(xlUp).Row ' Dynamically getting the last row in worksheet referenced by Ws1
Let Lr2 = Ws2.Range("A" & Ws2.Rows.Count & "").End(xlUp).Row ' Dynamically getting the last row in worksheet referenced by Ws2
Dim rngSrch As Range: Set rngSrch = Ws2.Range("A2:A" & Lr2 & "") ' The range that will be searched in
Dim rngDta As Range: Set rngDta = Ws1.Range("B2:B" & Lr1 & "") ' The range from which data will be looked for in rngSrch
Dim Cnt As Long ' For each rngDta.Item(Cnt)
For Cnt = Lr2 To 1 Step -1 ' We take -ve steps = we go backwards. This is important when deleteing things. See: https://excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=12902&viewfull=1#post12902
Dim MtchedCel As Variant
Set MtchedCel = rngSrch.Find(what:=rngDta.Item(Cnt), After:=rngSrch.Item(1), LookIn:=xlValues, LookAt:=xlWhole, SearchDirection:=xlNext, MatchCase:=True)
If Not MtchedCel Is Nothing Then ' Range.Find would return nothing if it did not find a match
' If it was Not Nothing then there was a match. So we do nothing
Else ' The attempt at a match failed, we got Nothing this is the condition to delete
rngDta.Rows(Cnt).EntireRow.Delete Shift:=xlUp ' The row is deleted , and so we have a space which is filled by shifting all rows in the worksheet Up
End If
Next Cnt ' Next rngDta.Item(Cnt)
Wb1.Close SaveChanges:=True ' Save the file and close it
Wb2.Close SaveChanges:=True ' Save the file and close it
End Sub
-
When I run the macro it does the following:
It considers values in Ws1 column B , ( that is the data range, rngData )
If it finds that value in Ws2 column A , then it does nothing
If it does not find that value, then it deletes that row in Ws1
With your data, it does not find ADANIPORTS or ADANIENT or ACC , so those data rows in Ws1 are deleted.
Looking at your Output worksheet, it would appear that you should be deleting many more rows in Ws1
I have tried to look back through the chaos of duplicated cross postings that you have done to try and geuss what you want….
The last macro I gave you is looping through the data in Ws1 using this code line
For Cnt = Lr2 To 1 Step -1
Most of the last macros you posted here and elsewhere were doing that……….
That is going to only loop through the data in Ws1 up to 36 ( because Lr2 is 36 )
I will take a guess that you want to loop through all the data in Ws1 , because I can see no reason why you should loop through just some of your data.
My guess is that you should be doing this
For Cnt = Lr1 To 2 Step -1
So
Try this next macro version:……
https://excelfox.com/forum/showthrea...ll=1#post14636
So finally what this macro is doing. In English:
Data values in Ws1 , (first worksheet in "1(sample).xls") column B , are looked for ( attempted to be matched ) to the column A range in Ws2 ( second worksheet in "H2(SAMPLE).xlsx")
If a match is found, then nothing is done. If a match was not found, then the entire row containing the data value in Ws1 is deleted
-
2 Attachment(s)
Hi Experts,
If column A of H2.xlsx matches with column B of 1.xls then delete that entire row by vba
macro will be placed in macro.xlsm and files can be located anywhere in the pc so hardcoded the path so that i can change it as per my needs
Thnx for the Help
sample file attached below
-
Problem Solved
Awesome Doc Sir
Thnx Alot for helping me in solving this Problem Sir
Have a Awesome Day
-
Your description is bad.
You have not said what worksheets are to be considered. It appears to be the second worksheet in "H2(SAMPLE).xlsx" and the first worksheet in "1(sample).xls"
So your last question I answered for you this morning was… , in English :
_ Sub Step3b() https://excelfox.com/forum/showthrea...ll=1#post14636
Data values in Ws1 , (first worksheet in "1(sample).xls") column B , are looked for ( attempted to be matched ) to the column A range in Ws2 ( second worksheet in "H2(SAMPLE).xlsx")
If a match is found, then nothing is done. If a match was not found, then the entire row containing the data value in Ws1 is deleted
Now, the question here is….
Data values in Ws1 , (first worksheet in "1(sample).xls") column B , are looked for ( attempted to be matched ) to the column A range in Ws2 ( second worksheet in "H2(SAMPLE).xlsx")
If a match is not found, then nothing is done. If a match is found, then the entire row containing the data value in Ws1 is deleted
It is sometime very hard to believe that you are incapable of figuring out the changes for yourself!
If you had left the ' comments on my macro then the changes would have been obvious
Previous macro,
Sub STEP3b() If a match is found, then nothing is done. If a match was not found, then the entire row containing the data value in Ws1 is deleted
Code:
If Not MtchedCel Is Nothing Then ' Range.Find would return nothing if it did not find a match
' If it was Not Nothing then there was a match. So we do nothing
Else ' The attempt at a match failed, we got Nothing this is the condition to delete
rngDta.Rows(Cnt).EntireRow.Delete Shift:=xlUp ' The row is deleted , and so we have a space which is filled by shifting all rows in the worksheet Up
End If
New Macro for If a match is not found, then nothing is done. If a match is found, then the entire row containing the data value in Ws1 is deleted
Code:
If Not MtchedCel Is Nothing Then ' Range.Find would return nothing if it did not find a match. Not Nothing is the condituion of a match, the condition to delete the row
rngDta.Rows(Cnt).EntireRow.Delete Shift:=xlUp ' The row is deleted , and so we have a space which is filled by shifting all rows in the worksheet Up
Else ' The attempt at a match failed, we got Nothing this is the condition to do nothing
' If it was Nothing then there was not a match. So we do nothing
End If
Or
Code:
If MtchedCel Is Nothing Then ' Range.Find would return nothing if it did not find a match. Nothing is the condituion of no match, the condition to do nothing
' If a match is not found, then nothing is done
Else ' The attempt at a match was succesful, we got a match, the condition to delete the row
rngDta.Rows(Cnt).EntireRow.Delete Shift:=xlUp ' The row is deleted , and so we have a space which is filled by shifting all rows in the worksheet Up
End If
Full macros here:
https://excelfox.com/forum/showthrea...ll=1#post14641
Alan
-
Thnx Alot Doc Sir for helping me in solving this problem
Problem Solved
Have a Awesome Day
From next Time, i will try to provide as much info as much i can provide
-
Problem 1 is very simple. You must have done this already hundreds of time! There is only one very important thing to remember: When looping to delete things, you must always loop backwards : https://excelfox.com/forum/showthrea...ll=1#post12902
Before:
_____ Workbook: ABC-PROBLEM1.xls ( Using Excel 2007 32 bit )
| Row\Col |
A |
B |
C |
D |
E |
F |
G |
H |
I |
J |
K |
|---|
| 1 |
Exchange |
Symbol |
Series/Expiry |
Open |
High |
Low |
Prev Close |
LTP |
|
|
|
| 2 |
NSE |
ACC |
EQ |
1265 |
1282.7 |
1246.5 |
1275.3 |
1247 |
22 |
BUY |
14104.65 |
| 3 |
NSE |
ADANIENT |
EQ |
151.85 |
165.45 |
151.4 |
151.85 |
152.35 |
25 |
BUY |
3235.03 |
| 4 |
NSE |
ADANIPORTS |
EQ |
348 |
348 |
348 |
346.55 |
338.85 |
15083 |
BUY |
3539.747 |
Run macro:
Code:
Sub DeleteRowifHighequalsLow_2() ' Problem 1 https://www.excelforum.com/excel-programming-vba-macros/1321862-delete-entire-row-by-vba.html https://excelfox.com/forum/showthread.php/2582-delete-entire-row-by-vbA
Rem 1 Worksheet info
Dim WbABC As Workbook, WsABC As Worksheet
Set WbABC = Workbooks.Open(ThisWorkbook.path & "\ABC-PROBLEM1.xls")
Set WsABC = WbABC.Worksheets.Item(1)
Dim LrABC As Long: Let LrABC = WsABC.Range("E" & WsABC.Rows.Count & "").End(xlUp).Row ' Make Lr dynamic https://excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=11466&viewfull=1#post11466
Dim arrABC() As Variant
Let arrABC() = WsABC.Range("A1:K" & LrABC & "").Value
Dim Cnt As Long
For Cnt = LrABC To 2 Step -1 ' When looping to delete things, you must always loop backwards : https://excelfox.com/forum/showthrea...ll=1#post12902
If arrABC(Cnt, 5) = arrABC(Cnt, 6) Then
WsABC.Rows(Cnt).EntireRow.Delete Shift:=xlUp ' The row is deleted , and so we have a space which is filled by shifting all rows in the worksheet Up
Else
End If
Next Cnt
End Sub
After:
_____ Workbook: ABC-PROBLEM1.xls ( Using Excel 2007 32 bit )
| Row\Col |
A |
B |
C |
D |
E |
F |
G |
H |
I |
J |
K |
|---|
| 1 |
Exchange |
Symbol |
Series/Expiry |
Open |
High |
Low |
Prev Close |
LTP |
|
|
|
| 2 |
NSE |
ACC |
EQ |
1265 |
1282.7 |
1246.5 |
1275.3 |
1247 |
22 |
BUY |
14104.65 |
| 3 |
NSE |
ADANIENT |
EQ |
151.85 |
165.45 |
151.4 |
151.85 |
152.35 |
25 |
BUY |
3235.03 |
| 4 |
|
|
|
|
|
|
|
|
|
|
|
Problem 2
See next post
-
Problem 2
From last post
Problem 2
You have not given me an After, and once again the explanation is bad. - you did not yet answer maniacb at excelforum.... https://www.excelforum.com/excel-pro...ml#post5365612
I will have to assume that this:
If column B of DEF.xlsx matches with Column I of ABC.xls then delete that entire row
Is supposed to mean this
If column B of DEF.xlsx matches with Column I of ABC.xls then delete that entire row of ABC.xls
In other words, something we have done many times already….
We consider the data in column I of ABC.xls from row 2. We try to find ( match ) that data to any row of data in column B of DEF.xlsx
If we do have a match then we delete the entire row containing the considered data from Column I of ABC.xls
I will do the first solution, Problem2a) slightly differently ,because I am getting bored answering almost the same question over and over again…
I am using vba arrays because for your values work that is the best approach in Excel ( https://excelfox.com/forum/showthrea...ll=1#post14628 )
I do not delete rows, so I do no backward looping
Instead I collect indices of the rows I want to have = rows which are not deleted. For you test data, the rows I want are 1 4 5 6 7 8 9 ( rows 2 and 3 ) are not wanted – you want to delete them )
Finally the macro replaces the original range with a new one containing just the rows you want
I also did a more conventional solution Problem2b like the ones you have seen a lot of in the last few days. It deletes the rows ( rows 2 and 3 )
Before
_____ Workbook: DEF PROBLEM 2.xlsx ( Using Excel 2007 32 bit )
| Row\Col |
A |
B |
C |
D |
E |
F |
G |
H |
I |
J |
K |
|---|
| 1 |
NSE |
22 |
6 |
> |
12755 |
A |
|
|
|
|
GTT |
| 2 |
NSE |
25 |
6 |
< |
13448 |
A |
|
|
|
|
GTT |
_____ Workbook: ABC.xls ( Using Excel 2007 32 bit )
| Row\Col |
A |
B |
C |
D |
E |
F |
G |
H |
I |
J |
K |
|---|
| 1 |
Exchange |
Symbol |
Series/Expiry |
Open |
High |
Low |
Prev Close |
LTP |
|
|
|
| 2 |
NSE |
ACC |
EQ |
1265 |
1282.7 |
1246.5 |
1275.3 |
1247 |
22 |
BUY |
14104.65 |
| 3 |
NSE |
ADANIENT |
EQ |
151.85 |
165.45 |
151.4 |
151.85 |
152.35 |
25 |
BUY |
3235.03 |
| 4 |
NSE |
ADANIPORTS |
EQ |
348 |
348 |
348 |
346.55 |
338.85 |
15083 |
BUY |
3539.747 |
| 5 |
NSE |
APOLLOHOSP |
EQ |
1359 |
1391 |
1339 |
1355.65 |
1346.8 |
157 |
BUY |
2994.65 |
| 6 |
NSE |
APOLLOPIPE |
EQ |
351.95 |
351.95 |
340 |
350.8 |
347.2 |
14361 |
BUY |
4339.566 |
| 7 |
NSE |
ASHOKLEY |
EQ |
52.1 |
52.35 |
51 |
52.7 |
51 |
212 |
BUY |
7051.82 |
| 8 |
NSE |
AUROPHARMA |
EQ |
789 |
805.45 |
775.35 |
796.95 |
782.4 |
275 |
BUY |
3608.73 |
| 9 |
NSE |
AXISBANK |
EQ |
426.5 |
435.55 |
408.7 |
427.45 |
413.5 |
5900 |
BUY |
255575.45 |
After
_____ Workbook: ABC.xls ( Using Excel 2007 32 bit )
| Row\Col |
A |
B |
C |
D |
E |
F |
G |
H |
I |
J |
K |
|---|
| 1 |
Exchange |
Symbol |
Series/Expiry |
Open |
High |
Low |
Prev Close |
LTP |
|
|
|
| 2 |
NSE |
ADANIPORTS |
EQ |
348 |
348 |
348 |
346.55 |
338.85 |
15083 |
BUY |
3539.747 |
| 3 |
NSE |
APOLLOHOSP |
EQ |
1359 |
1391 |
1339 |
1355.65 |
1346.8 |
157 |
BUY |
2994.65 |
| 4 |
NSE |
APOLLOPIPE |
EQ |
351.95 |
351.95 |
340 |
350.8 |
347.2 |
14361 |
BUY |
4339.566 |
| 5 |
NSE |
ASHOKLEY |
EQ |
52.1 |
52.35 |
51 |
52.7 |
51 |
212 |
BUY |
7051.82 |
| 6 |
NSE |
AUROPHARMA |
EQ |
789 |
805.45 |
775.35 |
796.95 |
782.4 |
275 |
BUY |
3608.73 |
| 7 |
NSE |
AXISBANK |
EQ |
426.5 |
435.55 |
408.7 |
427.45 |
413.5 |
5900 |
BUY |
255575.45 |
| 8 |
|
|
|
|
|
|
|
|
|
|
|
| 9 |
|
|
|
|
|
|
|
|
|
|
|
Macros
Here 2a
https://excelfox.com/forum/showthrea...ll=1#post14645
Or
Here 2b
https://excelfox.com/forum/showthrea...ll=1#post14646
