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

[fixer]

“Moderator” Notice
September 2020
Given up with this “OP” , Avinash around September 2020. https://excelfox.com/forum/showthread.php/2518-convert-the-data-from-xlsx-to-txt-file-Export-Excel-cell-values-to-delimeted-text-File?p=14972&viewfull=1#post14972
https://excelfox.com/forum/showthread.php/2518-convert-the-data-from-xlsx-to-txt-file-Export-Excel-cell-values-to-delimeted-text-File?p=14972&viewfull=1#post14972
I am no longer monitoring what its doings. It had curiosity appeal for a while, but even that has worn off me now!
It’s getting worse by the Day. Its still doing whatever it is that it is doing and getting Replies and answers at excelforum.com and likely a few places I don’t know about.






I will place the vba code in Report.xlsm
my all files are located in same place
Sheet name can be anything in both the files
I need the vba code that will open both the file and do the process and save the file
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(here entire row means the cells which matches delete that entire row)
and after the process close and save the file so that changes should be saved

http://www.vbaexpress.com/forum/showthread.php?65811-Delete-row

[DocAElstein]

Here is the test data : http://www.excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=11459&viewfull=1#post11459

__ "….my all files are located in same place, "….… assume that the file with the macro in is also in this same place, ( so my macro can use ThisWorkbook.Path to return the path that I need to open the files )

__ ….."…Sheet name can be anything in both the files…"…. I will assume that the sheet is the first tab sheet. This can be accessed by its Item number property … Worksheets.Item(1) ….



__ 1 ) To get some information, I used the macro recorder…. I open the files and delete row 2 of 1.xls
I did this whilst recording a macro . This was the result:

Sub Makro2()
'
' Makro2 Makro
'

' Open Workbooks
Workbooks.Open Filename:="F:\Excel0202015Jan2016\ExcelFox\vixer\1.xls"
Workbooks.Open Filename:= _
"F:\Excel0202015Jan2016\ExcelFox\vixer\BasketOrder. .xlsx"
'
' As example, delete row 2 in "1.xls"
Windows("1.xls").Activate

Rows("2:2").Select
Selection.Delete Shift:=xlUp

End Sub



__ 2 ) The new thing for you is the VBA rng.Find Method
Range,Find Method
https://stackoverflow.com/questions/49094391/excel-vba-range-findnext-v-range-find-what-have-i-missed
https://docs.microsoft.com/en-us/office/vba/api/excel.range.find
I look ( in code section Rem 3 ) for a cell ( as Range object ) in column C of basketorder.xlsx which matches with any cell of column B of 1.xlsx

( Important: I am then going to delete rows in a loop: We usually do such delete things in a backward loop: (Cnt = Lr2 To 1 Step -1 ). 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 ) )

Final Code and examples here:
http://www.excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=11461&viewfull=1#post11461
http://www.excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=11459&viewfull=1#post11459





Alan

[fixer]

Thnx Alot Doc Sir
Thnx for giving ur Precious time and Great Support to this post Sir
Thnx alot for giving ur Great Guidance Sir

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)
https://www.eileenslounge.com/viewtopic.php?p=303384#p303384 (https://www.eileenslounge.com/viewtopic.php?p=303384#p303384)
https://www.eileenslounge.com/viewtopic.php?p=302929#p302929 (https://www.eileenslounge.com/viewtopic.php?p=302929#p302929)
https://www.eileenslounge.com/viewtopic.php?p=302494#p302494 (https://www.eileenslounge.com/viewtopic.php?p=302494#p302494)
https://www.eileenslounge.com/viewtopic.php?p=302178#p302178 (https://www.eileenslounge.com/viewtopic.php?p=302178#p302178)
https://www.eileenslounge.com/viewtopic.php?p=301761#p301761 (https://www.eileenslounge.com/viewtopic.php?p=301761#p301761)
https://www.eileenslounge.com/viewtopic.php?p=301534#p301534 (https://www.eileenslounge.com/viewtopic.php?p=301534#p301534)
https://www.eileenslounge.com/viewtopic.php?p=301320#p301320 (https://www.eileenslounge.com/viewtopic.php?p=301320#p301320)
https://www.eileenslounge.com/viewtopic.php?p=301416#p301416 (https://www.eileenslounge.com/viewtopic.php?p=301416#p301416)
https://www.eileenslounge.com/viewtopic.php?p=301384#p301384 (https://www.eileenslounge.com/viewtopic.php?p=301384#p301384)
https://www.eileenslounge.com/viewtopic.php?p=300957#p300957 (https://www.eileenslounge.com/viewtopic.php?p=300957#p300957)
https://www.eileenslounge.com/viewtopic.php?p=300987#p300987 (https://www.eileenslounge.com/viewtopic.php?p=300987#p300987)
https://www.eileenslounge.com/viewtopic.php?f=27&t=38916&p=301354#p301354 (https://www.eileenslounge.com/viewtopic.php?f=27&t=38916&p=301354#p301354)
https://www.eileenslounge.com/viewtopic.php?p=303007#p303007 (https://www.eileenslounge.com/viewtopic.php?p=303007#p303007)
https://www.eileenslounge.com/viewtopic.php?f=27&t=38910&p=301028#p301028 (https://www.eileenslounge.com/viewtopic.php?f=27&t=38910&p=301028#p301028)
https://www.eileenslounge.com/viewtopic.php?p=300947#p300947 (https://www.eileenslounge.com/viewtopic.php?p=300947#p300947)
https://www.eileenslounge.com/viewtopic.php?p=300785#p300785 (https://www.eileenslounge.com/viewtopic.php?p=300785#p300785)
https://www.eileenslounge.com/viewtopic.php?p=300228#p300228 (https://www.eileenslounge.com/viewtopic.php?p=300228#p300228)
https://www.eileenslounge.com/viewtopic.php?p=300229#p300229 (https://www.eileenslounge.com/viewtopic.php?p=300229#p300229)
https://www.eileenslounge.com/viewtopic.php?p=299973#p299973 (https://www.eileenslounge.com/viewtopic.php?p=299973#p299973)
https://www.eileenslounge.com/viewtopic.php?p=299782#p299782 (https://www.eileenslounge.com/viewtopic.php?p=299782#p299782)
https://www.eileenslounge.com/viewtopic.php?f=41&t=38526&p=298390#p298390 (https://www.eileenslounge.com/viewtopic.php?f=41&t=38526&p=298390#p298390)
https://www.eileenslounge.com/viewtopic.php?p=297743#p297743 (https://www.eileenslounge.com/viewtopic.php?p=297743#p297743)
https://www.eileenslounge.com/viewtopic.php?p=297638#p297638 (https://www.eileenslounge.com/viewtopic.php?p=297638#p297638)
https://www.eileenslounge.com/viewtopic.php?p=297638#p297638 (https://www.eileenslounge.com/viewtopic.php?p=297638#p297638)
https://www.excelfox.com/forum/showthread.php/2909-Appendix-Thread-Evaluate-Range-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=24055&viewfull=1#post24055 (https://www.excelfox.com/forum/showthread.php/2909-Appendix-Thread-Evaluate-Range-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=24055&viewfull=1#post24055)
https://www.eileenslounge.com/viewtopic.php?p=297636#p297636 (https://www.eileenslounge.com/viewtopic.php?p=297636#p297636)
https://www.eileenslounge.com/viewtopic.php?f=27&t=38500&p=297591#p297591 (https://www.eileenslounge.com/viewtopic.php?f=27&t=38500&p=297591#p297591)
https://www.eileenslounge.com/viewtopic.php?p=297326#p297326 (https://www.eileenslounge.com/viewtopic.php?p=297326#p297326)
https://www.eileenslounge.com/viewtopic.php?p=297474#p297474 (https://www.eileenslounge.com/viewtopic.php?p=297474#p297474)
https://www.eileenslounge.com/viewtopic.php?p=296754#p296754 (https://www.eileenslounge.com/viewtopic.php?p=296754#p296754)
https://www.eileenslounge.com/viewtopic.php?p=296859#p296859 (https://www.eileenslounge.com/viewtopic.php?p=296859#p296859)
https://www.excelfox.com/forum/showthread.php/2918-Right-Hand-Side-Range-Range-Value-values-Range-Range-Value-only-sometimes-Range-Range-Value-Anomaly (https://www.excelfox.com/forum/showthread.php/2918-Right-Hand-Side-Range-Range-Value-values-Range-Range-Value-only-sometimes-Range-Range-Value-Anomaly)
https://www.excelfox.com/forum/showthread.php/2355-Tests-and-Notes-on-Range-Referrencing/page8 (https://www.excelfox.com/forum/showthread.php/2355-Tests-and-Notes-on-Range-Referrencing/page8)
https://www.eileenslounge.com/viewtopic.php?p=296859#p296859 (https://www.eileenslounge.com/viewtopic.php?p=296859#p296859)
https://www.excelfox.com/forum/showthread.php/2355-Tests-and-Notes-on-Range-Referrencing?p=24006&viewfull=1#post24006 (https://www.excelfox.com/forum/showthread.php/2355-Tests-and-Notes-on-Range-Referrencing?p=24006&viewfull=1#post24006)
https://www.excelfox.com/forum/showthread.php/2909-Appendix-Thread-Evaluate-Range-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=23185&viewfull=1#post23185 (https://www.excelfox.com/forum/showthread.php/2909-Appendix-Thread-Evaluate-Range-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=23185&viewfull=1#post23185)
https://www.eileenslounge.com/viewtopic.php?p=296344#p296344 (https://www.eileenslounge.com/viewtopic.php?p=296344#p296344)
https://www.eileenslounge.com/viewtopic.php?p=296617#p296617 (https://www.eileenslounge.com/viewtopic.php?p=296617#p296617)
https://www.eileenslounge.com/viewtopic.php?p=296145#p296145 (https://www.eileenslounge.com/viewtopic.php?p=296145#p296145)
https://www.eileenslounge.com/viewtopic.php?f=30&t=35100&p=295780#p295780 (https://www.eileenslounge.com/viewtopic.php?f=30&t=35100&p=295780#p295780)
https://www.eileenslounge.com/viewtopic.php?p=310720#p310720 (https://www.eileenslounge.com/viewtopic.php?p=310720#p310720)
https://www.eileenslounge.com/viewtopic.php?p=292967#p292967 (https://www.eileenslounge.com/viewtopic.php?p=292967#p292967)
https://www.eileenslounge.com/viewtopic.php?f=55&t=37867 (https://www.eileenslounge.com/viewtopic.php?f=55&t=37867)
https://www.eileenslounge.com/viewtopic.php?p=292965#p292965 (https://www.eileenslounge.com/viewtopic.php?p=292965#p292965)
https://www.eileenslounge.com/viewtopic.php?p=292965#p292965 (https://www.eileenslounge.com/viewtopic.php?p=292965#p292965)
https://www.eileenslounge.com/viewtopic.php?p=292453#p292453 (https://www.eileenslounge.com/viewtopic.php?p=292453#p292453)
https://www.eileenslounge.com/viewtopic.php?f=26&t=37808 (https://www.eileenslounge.com/viewtopic.php?f=26&t=37808)
https://www.eileenslounge.com/viewtopic.php?p=292391#p292391 (https://www.eileenslounge.com/viewtopic.php?p=292391#p292391)
https://www.eileenslounge.com/viewtopic.php?p=247121#p247121 (https://www.eileenslounge.com/viewtopic.php?p=247121#p247121)
https://www.eileenslounge.com/viewtopic.php?f=30&t=37732&p=292181#p292181 (https://www.eileenslounge.com/viewtopic.php?f=30&t=37732&p=292181#p292181)
https://www.eileenslounge.com/viewtopic.php?f=18&t=37707 (https://www.eileenslounge.com/viewtopic.php?f=18&t=37707)
https://www.eileenslounge.com/viewtopic.php?p=292019#p292019 (https://www.eileenslounge.com/viewtopic.php?p=292019#p292019)
https://www.eileenslounge.com/viewtopic.php?p=291976#p291976 (https://www.eileenslounge.com/viewtopic.php?p=291976#p291976)
https://www.eileenslounge.com/viewtopic.php?f=44&t=37709&p=291949#p291949 (https://www.eileenslounge.com/viewtopic.php?f=44&t=37709&p=291949#p291949)
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)

[DocAElstein]

Here is some extra info on making Lr dynamic:
http://www.excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=11466&viewfull=1#post11466
http://www.excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=11466&viewfull=1#post11466



Alan

[fixer]

Thnx Doc Sir for the info
Thnx Alot Sir
Have a Great Day

[fixer]

vba is placed in a sepearte file macro.xlsm
and there are two more files 1.xlsx & 2.xlsx
all files are located in different path
i am looking to slve this problem by vba macro
if column A of 1.xlsx matches with column A of of 2.xlsx then calculate the data in that row and if the number is positive then delete all the numbers from that entire row (numbers is starting from column B) & if it is negative then do nothing
example in current case acc matches dlf matches and tata matches
acc is negative (40-50=-10) then do nothing
dlf is positive (10+200=210) then delete the numbers which is starting from column B i.e 10 & 200
and so on
only macro.xlsm is open so we have to open that file

[DocAElstein]

Try this

Sub conditionally_delete_entire_row() ' http://www.excelfox.com/forum/showthread.php/2436-conditionally-delete-entire-row
Rem 1 workbooks, Worksheets info ' only macro.xlsm is open so we have to open that file
Dim Wb1 As Workbook, Wb2 As Workbook, Ws1 As Worksheet, Ws2 As Worksheet
Workbooks.Open Filename:=ThisWorkbook.Path & "\1.xlsx"
Set Wb1 = ActiveWorkbook
Set Ws1 = Wb1.Worksheets.Item("Sheet1") ' worksheet with string tab name of Sheet1
Set Wb2 = Workbooks.Open(Filename:=ThisWorkbook.Path & "\2.xlsx")
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, Rng2A As Range
Set Rng1A = Rng1.Range("A1:A" & Rng1.Rows.Count & ""): Set Rng2A = Rng2.Range("A1:A" & Rng2.Rows.Count & "")
Rem 2 take each cell in column A of 1.xlsx and compare it with each cell in column A of of 2.xlsx
Dim RngS1 As Range, RngS2 As Range
For Each RngS1 In Rng1A ' each cell in column A of 1
For Each RngS2 In Rng2A ' each cell in column A of 2
If RngS1.Value = RngS2.Value Then ' compare .. if column A of 1.xlsx matches with column A of of 2.xlsx
Dim DtaClc As Long ' calculate the data in that row
Let DtaClc = Application.WorksheetFunction.Sum(RngS2.Resize(1, Rng2.Columns.Count)) ' https://docs.microsoft.com/de-de/office/vba/api/excel.worksheetfunction.sum
If DtaClc > 0 Then ' delete all the numbers from that entire row (numbers is starting from column B)
'RngS2.Offset(0, 1).Resize(1, Rng2.Columns.Count - 1).Delete shift:=xlToLeft
RngS2.Offset(0, 1).Resize(1, Rng2.Columns.Count - 1).ClearContents
Else
' Dtaclc is negative then do nothing
End If
Else
' column A of 1.xlsx does not match with column A of of 2.xlsx - do nothing
End If
Next RngS2
Next RngS1
End Sub

[fixer]

Thnx Alot Doc Sir for ur Great Effort Sir
Problem Solved

[fixer]

vba is placed in a seperate files macro.xlsm
& there are two more files 2.xlsx and 1.xlsx
all files are located in a different place
i uploaded the pic of 1.xlsx & 2.xlsx plz look sir
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
in this example 3rd row will be deleted of 2.xlsx
i need the macro of the same sir
so plz have a look into this problem and help me in solving this problem sir
plz see the sample file

[DocAElstein]

Change to this

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 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
End Sub

Before:-
_____ Workbook: 1.xlsx ( Using Excel 2007 32 bit )
Row\Col
A
B
C

1


2ACC
6


3DLF
4


4
Worksheet: Sheet1

_____ Workbook: 2.xlsx ( Using Excel 2007 32 bit )
Row\Col
A
B
C
D
E
F
G

1Exchange


2NSEACCEQ
10
11
12
13


3NSEADANIENTEQ
8
7
6
5


4NSEDLFEQ
1
2
3
4


5
Worksheet: Sheet1


After:

_____ Workbook: 1.xlsx ( Using Excel 2007 32 bit )
Row\Col
A
B
C

1


2ACC
6


3DLF
4


4
Worksheet: Sheet1

_____ Workbook: 2.xlsx ( Using Excel 2007 32 bit )
Row\Col
A
B
C
D
E
F

1Exchange


2NSEACCEQ
10
11
12


3NSEDLFEQ
1
2
3


4


5
Worksheet: Sheet1

[fixer]

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

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]

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

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
2807


'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]

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]

On Error Resume Next is one of a few ways to do “Error handling in VBA” ( http://www.excelfox.com/forum/showthread.php/2239-Resume-On-Error-GoTo-0-1-GoTo-Error-Handling-Statements-Runtime-VBA-Error-Handling-ORNeRe-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

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


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


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]

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]

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]

The last macro of yours ( http://www.excelfox.com/forum/showthread.php/2436-conditionally-delete-entire-row?p=12882&viewfull=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]

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]

Before:- ( from your uploaded files )
_____ Workbook: sample1.xlsx ( Using Excel 2007 32 bit )
Row\Col
A
B
C
D

1


2ACC
150


3DLF
120


4


5match column A of sample1.xlsx with column B of sample2.xlsx


6If it matches then do nohing


7if 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

1ExchangeSymbolSeries/ExpiryOpenHighLow Prev CloseLTP


2NSEACCEQ
1034
1068.75
988
998.45
998.45


3NSEADANIENTEQ
123.05
135
118.2
120.9
120.9This entire row will be deleted


4NSEDLFEQ
235
235.8
203
207.8
207.8


5
Worksheet: Sheet1

Now I run this


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
C

1


2ACC
150


3DLF
120


4
Worksheet: Sheet1

_____ Workbook: sample2.xlsx ( Using Excel 2007 32 bit )
Row\Col
A
B
C
D
E
F
G
H
I

1ExchangeSymbolSeries/ExpiryOpenHighLow Prev CloseLTP


2NSEACCEQ
1034
1068.75
988
998.45
998.45


3NSEDLFEQ
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...

[fixer]

I uploaded my real file plz have a look bcoz i am runing the code and i am not getting any result
Plz have a look Doc Sir

[DocAElstein]

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 )
Row\Col
A
B
C

1


2ACC
100


3DLF
150


4
Worksheet: Sheet1

_____ Workbook: 1.xls ( Using Excel 2007 32 bit )
Row\Col
A
B
C
D
E
F
G
H
I

1ExchangeSymbolSeries/ExpiryOpenHighLow Prev CloseLTP


2NSEACCEQ
1014
1030
955.5
998.45
957.4


3NSEADANIPOWEREQ
27.35
27.75
25.65
25.65
25.85


4NSEDLFEQ
390
419.7
350.05
387.25
352.4


5NSEAMBUJACEMEQ
145.1
155.8
142.55
145.85
145


6NSEAPOLLOHOSPEQ
1250
1265
1124.15
1158.65
1127.05


7
Worksheet: 1-Sheet1


Same macro as before ( http://www.excelfox.com/forum/showthread.php/2436-conditionally-delete-entire-row?p=12887&viewfull=1#post12887 )
Run macro ...


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 )
Row\Col
A
B
C

1


2ACC
100


3DLF
150


4
Worksheet: Sheet1

_____ Workbook: 1.xls ( Using Excel 2007 32 bit )
Row\Col
A
B
C
D
E
F
G
H
I

1ExchangeSymbolSeries/ExpiryOpenHighLow Prev CloseLTP


2NSEACCEQ
1014
1030
955.5
998.45
957.4


3NSEDLFEQ
390
419.7
350.05
387.25
352.4


4NSEAMBUJACEMEQ
145.1
155.8
142.55
145.85
145


5NSEAPOLLOHOSPEQ
1250
1265
1124.15
1158.65
1127.05


6
Worksheet: 1-Sheet1


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......

[fixer]

Now i understood what is the issue
Doc Sir everything will be deleted in this sheet see the sample pic only the row that has ACC & dLf that row will be there and rest will be deleted
see the sample pic and delete rest data except acc and dlf

[DocAElstein]

You must try to understand and explain what you want.

These two are a contradiction:
You say before:
If column A of STEP1U.xls matches with column B of 1.xls then keep that entire row of 1.xls & if not matches then delete the entire row of 1.xls

Now you say…. only the row that has ACC & dLf that row will be there …

. But

in row 3 we have
DLF column A __ ADANIPOWER column B

In row 4 we have
Column A empty __ DLF in column B

So in those rows are no match = not matches

DLF column A <> ADANIPOWER column B
Column A empty <> DLF in column B

Either
You do not know yourself what you want
Or
You are totally unable to explain what you want.


It is impossible to help you if you can not explain what it is you want.
Either try again to explain, or give better data to show what you want


As always, the problem is not the macro. The macros always do what you ask for. But you always ask wrong.
God may know what you want. But I am not God. :)
If you are unable to explain , then at least give much better data to show what you want.

[DocAElstein]

How about this….

What is wanted….
….. I have two workbooks, STEP1U.xlsb and 1.xls
There is a data range in the first worksheet of both workbooks, from rows 2. ( I have a header range in rows 1 )

Compare/ Consider column A in the first worksheet of STEP1U.xlsb with column B in the first worksheet of 1.xls. ( Ignore the first row – this is the header row )

If the value in column B in the first worksheet of 1.xls is not in column A in the first worksheet of STEP1U.xlsb , then the entire row , ( in 1.xls ), containing that value is to be deleted.
( STEP1U.xlsb is not changed )

Alternative short explanation: Delete all the rows in 1.xls whose value in column B is not to be found in column A in STEP1U.xlsb. ( STEP1U.xlsb remains unchanged )


Before:-
_____ Workbook: STEP1U.xlsb ( Using Excel 2007 32 bit )
Row\Col
A
B
C

1


2ACC
100


3DLF
150


4
Worksheet: Sheet1

_____ Workbook: 1.xls ( Using Excel 2007 32 bit )
Row\Col
A
B
C
D
E
F
G
H
I

1ExchangeSymbolSeries/ExpiryOpenHighLow Prev CloseLTP


2NSEACCEQ
1014
1030
955.5
998.45
957.4


3NSEADANIPOWEREQ
27.35
27.75
25.65
25.65
25.85


4NSEDLFEQ
390
419.7
350.05
387.25
352.4


5NSEAMBUJACEMEQ
145.1
155.8
142.55
145.85
145


6NSEAPOLLOHOSPEQ
1250
1265
1124.15
1158.65
1127.05


7
Worksheet: 1-Sheet1


After :-

_____ Workbook: STEP1U.xlsb ( Using Excel 2007 32 bit )
Row\Col
A
B
C

1


2ACC
100


3DLF
150


4
Worksheet: Sheet1

_____ Workbook: 1.xls ( Using Excel 2007 32 bit )
Row\Col
A
B
C
D
E
F
G
H
I

1ExchangeSymbolSeries/ExpiryOpenHighLow Prev CloseLTP


2NSEACCEQ
1014
1030
955.5
998.45
957.4


3NSEDLFEQ
390
419.7
350.05
387.25
352.4


4


5
Worksheet: 1-Sheet1




Solution:-

See here:
http://www.excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=12896&viewfull=1#post12896






See also some Important notes on looping backwards when deleting rows
http://www.excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=12902&viewfull=1#post12902

[fixer]

Sorry Doc Sir for a mistake
Thnx Alot Doc Sir for ur Great effort & Great help
Problem Solved









Moderator Translation:
I have managed to totally confuse myself and everyome else by posting gibberish that I don’t even understand myself, so lets just chuck in one of my canned replies, ( that I don’t understand either ) , and just get out of here…
The a few days ler after multiple cross duplicated poostings I am a total fucking idiot dim pig shit for brains, so just give me a macro that does what I want, even though most of the time I aint got a fucking clue myself what I want because I am such a total fucking spacko ... and never mind anyway, fuck off you mug, as some other twat gave me a macro at one of my many duplicated cross postings.......( Intro to Avinash Singh... https://excelfox.com/forum/showthread.php/2278-Misc-Leonardo1234-rider-1234-vixer-Highlighting-Simple-Early-stuff-Avinash-Introduction )

[fixer]

there are two files file name sample1.xlsx & sample2.xlsx
vba will be placed in a seperate file macro.xlsm
sample1.xlsx is located at C:\Users\WolfieeeStyle\Desktop
sample2.xlsx is located at C:\Users\WolfieeeStyle\Desktop\Upstox
macro.xlsm is located at C:\Users\WolfieeeStyle\Desktop
only macro.xlsm file is opened so we have to open rest 2 file by vba
If column H of sample2.xlsx matches with Column D then look column B data of sample2.xlsx and find that data in sample1.xlsx in column B and after getting that data in sample1.xlsx in column B , copy that entire row of sample1.xlsx and paste that in sample2.xlsx in the same row
all this is have to do by vba
so plz have a look into this problem and help me in solving this problem sir
sheet name can be anything

[DocAElstein]

macro will be placed in a separate file macro.xlsm
only macro.xlsm file is opened so we have to open rest 2 file by vba
sample1.xlsx is located at C:\Users\WolfieeeStyle\Desktop
sample2.xlsx is located at C:\Users\WolfieeeStyle\Desktop\Upstox
macro.xlsm is located at C:\Users\WolfieeeStyle\Desktop

So … in macro.xlsm means Thisworkbook.Path is C:\Users\WolfieeeStyle\Desktop

So … Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\sample1.xlsx") is Workbooks.Open(ThisWorkbook.Path & "\sample1.xlsx")
and Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\Upstox\sample2.xlsx") is Workbooks.Open(ThisWorkbook.Path & "\Upstox\sample2.xlsx")

sheet name can be anything
So , for first worksheet, use item number, Worksheets.Item(1)
( second worksheet is Worksheets.Item(2)


Before:
_____ Workbook: sample1.xlsx ( Using Excel 2007 32 bit )
Row\Col
A
B
C
D
E
F
G
H
I

1ExchangeSymbolSeries/ExpiryOpenHighLow Prev CloseLTP


2NSEACCEQ
1014
1030
955.5
998.45
957.4


3NSEADANIPORTSEQ
27.35
27.75
25.65
25.65
25.85


4
Worksheet: Tabelle1

_____ Workbook: sample2.xlsx ( Using Excel 2007 32 bit )
Row\Col
A
B
C
D
E
F
G
H
I

1ExchangeSymbolSeries/ExpiryOpenHighLow Prev CloseLTP


2NSEACCEQ
1014
1030
955.5
998.45
957.4


3NSEADANIPORTSEQ
27.35
28
29
30
27.35


4
Worksheet: Tabelle2


Run macro:

' ThisWorkbook is macro.xlsm is locatred at C:\Users\WolfieeeStyle\Desktop
' so ThisWorkbook.path = "C:\Users\WolfieeeStyle\Desktop"
Sub conditionally_replaceentirerow() ' http://www.excelfox.com/forum/showthread.php/2438-replace-the-entire-row
Rem 1 Worksheets info
Dim Wb1 As Workbook, Wb2 As Workbook, Ws1 As Worksheet, Ws2 As Worksheet
Set Wb1 = Workbooks("sample1.xlsx")
'Set Wb1 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\sample1.xlsx")
'Set Wb1 = Workbooks.Open(ThisWorkbook.Path & "\sample1.xlsx")
Set Ws1 = Wb1.Worksheets.Item(1) ' worksheet of first tab
Set Wb2 = Workbooks("sample2.xlsx") '
'Set Wb2 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\Upstox\sample2.xlsx")
'Set Wb2 = Workbooks.Open(ThisWorkbook.Path & "\Upstox\sample2.xlsx")
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

Rem 2 Delete an entire row in Ws2 if value in column B is not anywhere in column A of Ws1
Dim Rws As Long
For Rws = 2 To Rng2.Rows.Count
If Rng2.Range("H" & Rws & "").Value = Rng2.Range("D" & Rws & "").Value Then ' If column H of sample2.xlsx matches with Column D then
Dim rngFnd As Range
Set rngFnd = Rng1.Range("B2:B" & Rng2.Rows.Count).Find(what:=Rng2.Range("B" & Rws & "").Value, After:=Rng1.Range("B2"), LookIn:=xlValues, LookAt:=xlPart, searchorder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False) ' https://stackoverflow.com/questions/49094391/excel-vba-range-findnext-v-range-find-what-have-i-missed/49181464#49181464
If rngFnd Is Nothing Then ' The value from column B in Ws2 was not found in column B of Ws1
' do nothing
Else
rngFnd.Offset(0, -1).Resize(1, Rng2.Columns.Count).Copy ' B offest -1 is A resize to all columns in range B
Rng2.Range("A" & Rws & "").PasteSpecial Paste:=xlPasteAllUsingSourceTheme '
End If
Else
' column H of sample2.xlsx DOES NOT matches with Column D do nothing
End If
Next Rws

' Wb1.Save
' Wb1.Close
' Wb2.Save
' Wb2.Close
End Sub






After Results

_____ Workbook: sample2.xlsx ( Using Excel 2007 32 bit )
Row\Col
A
B
C
D
E
F
G
H
I

1ExchangeSymbolSeries/ExpiryOpenHighLow Prev CloseLTP


2NSEACCEQ
1014
1030
955.5
998.45
957.4


3NSEADANIPORTSEQ
27.35
27.75
25.65
25.65
25.85


4
Worksheet: Tabelle2



sample2.xlsx : https://app.box.com/s/xavyjz9q6ek3qknam42yif4f7l0qxdfa

[fixer]

Awesome Doc Sir Thnx Alot for ur Great Effort Doc Sir
Problem Solved

[fixer]

Hi, I am looking for a macro that will do the things as mentioned below

If column J of 1.xls has buy & column H of 1.xls is not greater than column D of 1.xls then match column I data of 1.xls with column B of alert.xls and if it matches then delete that entire row of alert.xls
If column J of 1.xls has a blank cell then match column I data of 1.xls with column B of alert.xls and if it matches then delete that entire row of alert.xls
If column J of 1.xls has short & column H of 1.xls is Greater than than column D of 1.xls then match column I data of 1.xls with column B of alert.xls and if it matches then delete that entire row of alert.xls



These are the three condition that macro will follow and based on that it will work
all files are located in different path, macro will be placed in separte file macro.xlsm
sheet name can be anything

https://www.excelforum.com/excel-programming-vba-macros/1317589-conditionally-compare-the-data-and-delete-entire-row.html#post5340172
This link has solved the solution by considering alert.csv but now it is changed to alert.xls

[DocAElstein]

...https://www.excelforum.com/excel-programming-vba-macros/1317589-conditionally-compare-the-data-and-delete-entire-row.html#post5340172
This link has solved the solution by considering alert.csv but now it is changed to alert.xls
The solution here, https://www.excelforum.com/excel-programming-vba-macros/1317589-conditionally-compare-the-data-and-delete-entire-row.html#post5340172 , was optimised for using a text file as Alert.csv , using advanced Text file data manipulation techniques
There is no useful coding information there which can be applied to the current requirement : The macro from jindon is no use in the current question here.



If column J of 1.xls has BUY & column H of 1.xls is not greater than column D of 1.xls then match column I data of 1.xls with column B of alert.xls and if it matches then delete that entire row of alert.xls
If column J of 1.xls has a blank cell then match column I data of 1.xls with column B of alert.xls and if it matches then delete that entire row of alert.xls
If column J of 1.xls has SHORT & column H of 1.xls is Greater than column D of 1.xls then match column I data of 1.xls with column B of alert.xls and if it matches then delete that entire row of alert.xls
row 3 & row 4 & row 5 will be deleted after running the macro

Before:
_____ Workbook: 1.xls ( Using Excel 2007 32 bit )
Row\ColABCDEFGHIJ
1ExchangeSymbolSeries/ExpiryOpenHighLow Prev CloseLTP

2NSEACCEQ
1183.72
1240
1161.6
1227.1
1227.1
22BUY

3NSEADANIENTEQ
139.38
141.2
136.6
138.1
140
25BUY

4NSEADANIPORTSEQ
311.85
315
306.55
310.6
312
15083SHORT

5NSEADANIPOWEREQ
33.165
34.5
32.85
33
33.2
17388SHORT

6NSEAMARAJABATEQ
594
613.5
586.9
592.55
594
100

7NSEASIANPAINTEQ
1584.488
1625
1555.4
1617.9
1617.9
236BUY
Worksheet: 1-Sheet1 9 June

_____ Workbook: Alert.xls ( Using Excel 2007 32 bit )
Row\ColABCDEFGHIJK
1NSE
236
6>
431555AGTT

2NSE
25
6>
431555AGTT

3NSE
15083
6>
431555AGTT

4NSE
17388
6>
431555AGTT

5NSE
100
6>
431555AGTT

6NSE
22
6>
431555AGTT
Worksheet: Alert 9 June xls

After running macro here: https://excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=13733&viewfull=1#post13733


_____ Workbook: Alert.xls ( Using Excel 2007 32 bit )
Row\ColABCDEFGHIJK
1NSE
236
6>
431555AGTT

2NSE
25
6>
431555AGTT

3NSE
22
6>
431555AGTT

4

5

6
Worksheet: Alert 9 June xls



Alan




macro.xlsm : https://app.box.com/s/z358r7tbc9hzthi539dlj49jsf4gyg8p
1.xls : https://app.box.com/s/38aoip5xi7018y9syt0xe4g04u95l6xk
Alert.xls : https://app.box.com/s/ectstkrcfnuozys9tmdd0qi3tdvyxb3w

[fixer]

Problem Solved
Thnx Alot Doc Sir for helping me in solving this problem Sir

[fixer]

This was the Macro

Sub STEP3()
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)
Set Ws2 = Wb2.Worksheets.Item(2)
Dim Lr1 As Long, Lr2 As Long:
Let Lr1 = Ws1.Range("A" & Ws1.Rows.Count & "").End(xlUp).Row
Let Lr2 = Ws1.Range("A" & Ws1.Rows.Count & "").End(xlUp).Row
Dim rngSrch As Range: Set rngSrch = Ws2.Range("A2:A" & Lr2 & "")
Dim rngDta As Range: Set rngDta = Ws1.Range("B2:B" & Lr1 & "")

Dim Cnt As Long
For Cnt = Lr2 To 1 Step -1
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

Else
rngDta.Rows(Cnt).EntireRow.Delete Shift:=xlUp
End If

Next Cnt
Wb1.Close SaveChanges:=True
Wb2.Close SaveChanges:=True
End Sub


I changed this Macro As per my needs but getting error
So Plz have a look Sir

Sub STEP6()
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)
Set Ws2 = Wb2.Worksheets.Item(2)
Dim Lr1 As Long, Lr2 As Long:
Let Lr1 = Ws1.Range("A" & Ws1.Rows.Count & "").End(xlUp).Row
Let Lr2 = Ws1.Range("A" & Ws1.Rows.Count & "").End(xlUp).Row
Dim rngSrch As Range: Set rngSrch = Ws2.Range("A2:A" & Lr2 & "")
Dim rngDta As Range: Set rngDta = Ws1.Range("B2:B" & Lr1 & "")

Dim Cnt As Long
For Cnt = Lr2 To 1 Step -1
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
rngDta.Rows(Cnt).EntireRow.Delete Shift:=xlUp
Else

End If

Next Cnt
Wb1.Close SaveChanges:=True
Wb2.Close SaveChanges:=True
End Sub


Plz see the else statement in both the macros

Error which i got, I uploaded the pic of the same




https://eileenslounge.com/viewtopic.php?f=30&t=34937&p=271392#p271392



Plz run the macro the file which i have uploaded

[DocAElstein]

We are back to about a year ago, around here
https://excelfox.com/forum/showthread.php/2364-Delete-rows-based-on-match-criteria-in-two-excel-files?p=11467&viewfull=1#post11467
https://excelfox.com/forum/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=12896&viewfull=1#post12896
https://excelfox.com/forum/showthread.php/2364-Delete-rows-based-on-match-criteria-in-two-excel-files?p=12897&viewfull=1#post12897


problem solved .. please delete Thread
No. Problem is not solved
I still see at least two issues

Problem 1
You still do not understand Lr issue. You still have it wrong….
Lr variable, we have typically used in many of your macros , ( in the meantime well over a hundred similar macros ) , to determine dynamically the last used cell in a column.

For example, in your macro, for the first column in the two worksheets :-
Lr1 is for last row dynamically found for worksheet, Ws1
Lr2 is for last row dynamically found for worksheet, Ws2

So it should be

Let Lr1 = Ws1.Range("A" & Ws1.Rows.Count & "").End(xlUp).Row
Let Lr2 = Ws2.Range("A" & Ws2.Rows.Count & "").End(xlUp).Row

Problem 2


If Not MtchedCel Is Nothing Then
rngDta.Rows(Cnt).EntireRow.Delete Shift:=xlUp
Else

End If


Or


If Not MtchedCel Is Nothing Then

Else
rngDta.Rows(Cnt).EntireRow.Delete Shift:=xlUp
End If


You always get that mixed up. Problem is English language and general idiocy. You may never know what you want. Half the time you will get it correct. The other half of the time you will get it wrong.



Most of the time it continues to appear as if you either do not read or understand anything that anyone anywhere ever writes for you.
You continue to go around in chaotic circles of multiple duplicate cross posting of the same or very similar issues, and seem to be starting again recently, with even more cross posting of very similar questions.
Because of this you are now experiencing even more problems: Everyone has a different posting style, and there are usually many ways to solve a problem in VBA. So you are producing a chaotic collection of coding written by many different people, in many different ways. This collection of coding, some working perfectly, some not erroring, but not working properly, you will never, ever, be able to combine to achieve anything over than a total chaotic mess….

[fixer]

Problem 1)


Sub STEP3()
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)
Set Ws2 = Wb2.Worksheets.Item(2)
Dim Lr1 As Long, Lr2 As Long:
Let Lr1 = Ws1.Range("A" & Ws1.Rows.Count & "").End(xlUp).Row
Let Lr2 = Ws1.Range("A" & Ws1.Rows.Count & "").End(xlUp).Row
Dim rngSrch As Range: Set rngSrch = Ws2.Range("A2:A" & Lr2 & "")
Dim rngDta As Range: Set rngDta = Ws1.Range("B2:B" & Lr1 & "")

Dim Cnt As Long
For Cnt = Lr2 To 1 Step -1
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

Else
rngDta.Rows(Cnt).EntireRow.Delete Shift:=xlUp
End If

Next Cnt
Wb1.Close SaveChanges:=True
Wb2.Close SaveChanges:=True
End Sub


R u talking about this Doc Sir
I have used this macro
Do u mean to say that
I have to use this in this macro

Let Lr1 = Ws1.Range("A" & Ws1.Rows.Count & "").End(xlUp).Row
Let Lr2 = Ws2.Range("A" & Ws2.Rows.Count & "").End(xlUp).Row

Problem 2

https://eileenslounge.com/viewtopic.php?f=30&t=34937

U r talking about this
so may i know what changes is required in the second problem so that i can put the same & be safe




3)Thnx Bro for all of ur Great Efforts, this is Best thing about u
Thnx for knowing me about the problems
& Yes this question i asked u in last year , & it is working fine
I just want to make the system Awesome Like u Bro
U r the one of the best person i ever met
Thnx for keeping a eye into all of my problems that i may have infuture

[DocAElstein]

Problem 1)
Problem 1)
Do u mean to say that
I have to use this in this macro

Let Lr1 = Ws1.Range("A" & Ws1.Rows.Count & "").End(xlUp).Row
Let Lr2 = Ws2.Range("A" & Ws2.Rows.Count & "").End(xlUp).Row..
Yes

Problem 2)
This is not a VBA problem.
You cant speak English or understand any simple thing in English.
I am not sure what the solution to such problems are.
Possibly try to leave in some 'green comments so then later you will remember what code line is doing. Then later it is easier to remember and later it is easier to see where is problem
'green comments do not cause any problems
'green comments do not make any coding slower
There will never be issues caused by 'green comments
'green comments can stay for ever. They do no harm
VBA does not see 'green comments. So VBA is not effected by 'green comments For VBA , 'green comments are invisible. So 'green comments cannot have any effect on any coding
There will never be any problems caused by 'green comments
There can never be any problems caused by 'green comments
A code line can be very long. So you can have a very long ling full of important information so that later you can see what all code line are doing.
So if you have lots of 'green comments you will not need to remember, because all information is there for you to read later if you need to know again what is the code doing.

But everyone has a different choice. You can choose

Some people use no 'green comments
Some people use some 'green comments
Some people use lots of 'green comments
I use very many 'green comments because I am not smart. I am stupid. I forget everything. I am not a computer programmer professional. I am a stupid Builder. I always forget what my macros do. So I choose to write lots of 'green comments. Then later his helps me to remember what I did and what to do again in the future

[fixer]

Sub STEP3()
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)
Set Ws2 = Wb2.Worksheets.Item(2)
Dim Lr1 As Long, Lr2 As Long:
Let Lr1 = Ws1.Range("A" & Ws1.Rows.Count & "").End(xlUp).Row
Let Lr2 = Ws2.Range("A" & Ws2.Rows.Count & "").End(xlUp).Row
Dim rngSrch As Range: Set rngSrch = Ws2.Range("A2:A" & Lr2 & "")
Dim rngDta As Range: Set rngDta = Ws1.Range("B2:B" & Lr1 & "")

Dim Cnt As Long
For Cnt = Lr2 To 1 Step -1
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

Else
rngDta.Rows(Cnt).EntireRow.Delete Shift:=xlUp
End If

Next Cnt
Wb1.Close SaveChanges:=True
Wb2.Close SaveChanges:=True
End Sub


I updated the macro Sir & i will use this macro
& Now problem is solved Doc Sir
any more updates & suggestions are there then plz let me know Doc Sir
& Thnx For ur Great Suggestion & for helping me in solving this problem Sir

[DocAElstein]

....
any more updates & suggestions are there then plz let me know...
I would add comments. But that is just personal choice. Most people do not like to have comments.

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

[fixer]

No Problem Doc Sir
Thnx Alot for giving ur Precious Time & Providing me the Great info about this Macro Doc Sir
Problem Solved
Have a Awesome Day

[fixer]

Problem 1
Hi Experts,
I am looking for a macro that will do the below things

Macro will be placed in macro.xlsm(so we have to open abc by a macro)
If column E matches with Column F then delete that entire row by vba
and save the changes & close the workbook
File ABC.xls may be located anywhere in the pc, so plz hardcode the path in the macro,so that i can change it as per my needs in future

Thnx For the Help

Sample file attached below

Problem 2

Hi Experts,
I am looking for a macro that will do the below things

Macro will be placed in macro.xlsm(so we have to open abc by a macro)
If column B of DEF.xlsx matches with Column I of ABC.xls then delete that entire row
and save the changes & close the workbook
File ABC.xls & DEF.xlsx may be located anywhere in the pc, so plz hardcode the path in the macro,so that i can change it as per my needs in future

Thnx For the Help

Sample file attached below





https://www.excelforum.com/excel-programming-vba-macros/1321862-delete-entire-row-by-vba.html#post5365302

[fixer]

.....

[fixer]

I would like to notify something about this macro and about this post
in this macro

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

[DocAElstein]

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\ColA
2ADANIPOWER

3AMARAJABAT

4ASIANPAINT
Worksheet: Sheet2

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\ColA
2ADANIPOWER

3AMARAJABAT

4ASIANPAINT

5

6

7

8

9

10
Worksheet: Sheet2

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\ColA
2ADANIPOWER

3AMARAJABAT

4ASIANPAINT

5AMBUJACEM

6APOLLOHOSP

7APOLLOPIPE
Worksheet: Sheet2

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\ColA
2ADANIPOWER

3AMARAJABAT
Worksheet: Sheet2

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

[fixer]

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)

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

[DocAElstein]

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/showthread.php/2577-Appendix-Thread-(-Codes-for-other-Threads-(-Avinash-)-)?p=14636&viewfull=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

[fixer]

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

[fixer]

Problem Solved
Awesome Doc Sir
Thnx Alot for helping me in solving this Problem Sir
Have a Awesome Day

[DocAElstein]

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/showthread.php/2577-Appendix-Thread-(-Codes-for-other-Threads-(-Avinash-)-)?p=14636&viewfull=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

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

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


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/showthread.php/2577-Appendix-Thread-(-Codes-for-other-Threads-(-Avinash-)-)?p=14641&viewfull=1#post14641




Alan

[fixer]

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

[DocAElstein]

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/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=12902&viewfull=1#post12902

Before:
_____ Workbook: ABC-PROBLEM1.xls ( Using Excel 2007 32 bit )
Row\ColABCDEFGHIJK
1ExchangeSymbolSeries/ExpiryOpenHighLow Prev CloseLTP

2NSEACCEQ
1265
1282.7
1246.5
1275.3
1247
22BUY
14104.65

3NSEADANIENTEQ
151.85
165.45
151.4
151.85
152.35
25BUY
3235.03

4NSEADANIPORTSEQ
348
348
348
346.55
338.85
15083BUY
3539.747
Worksheet: 1-Sheet1

Run macro:

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/showthread.php/2345-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=12902&viewfull=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\ColABCDEFGHIJK
1ExchangeSymbolSeries/ExpiryOpenHighLow Prev CloseLTP

2NSEACCEQ
1265
1282.7
1246.5
1275.3
1247
22BUY
14104.65

3NSEADANIENTEQ
151.85
165.45
151.4
151.85
152.35
25BUY
3235.03

4
Worksheet: 1-Sheet1



Problem 2
See next post

[DocAElstein]

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-programming-vba-macros/1321862-delete-entire-row-by-vba.html#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/showthread.php/2577-Appendix-Thread-(-Codes-for-other-Threads-(-Avinash-)-)?p=14628&viewfull=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\ColABCDEFGHIJK
1NSE226>12755AGTT

2NSE256<13448AGTT
Worksheet: DEF

_____ Workbook: ABC.xls ( Using Excel 2007 32 bit )
Row\ColABCDEFGHIJK
1ExchangeSymbolSeries/ExpiryOpenHighLow Prev CloseLTP

2NSEACCEQ12651282.71246.51275.3124722BUY14104.65

3NSEADANIENTEQ151.85165.45151.4151.85152.3525BUY32 35.03

4NSEADANIPORTSEQ348348348346.55338.8515083BUY3539. 747

5NSEAPOLLOHOSPEQ1359139113391355.651346.8157BUY299 4.65

6NSEAPOLLOPIPEEQ351.95351.95340350.8347.214361BUY4 339.566

7NSEASHOKLEYEQ52.152.355152.751212BUY7051.82

8NSEAUROPHARMAEQ789805.45775.35796.95782.4275BUY36 08.73

9NSEAXISBANKEQ426.5435.55408.7427.45413.55900BUY25 5575.45
Worksheet: 1-Sheet1 Problem 2

After
_____ Workbook: ABC.xls ( Using Excel 2007 32 bit )
Row\ColABCDEFGHIJK
1ExchangeSymbolSeries/ExpiryOpenHighLow Prev CloseLTP

2NSEADANIPORTSEQ348348348346.55338.8515083BUY3539. 747

3NSEAPOLLOHOSPEQ1359139113391355.651346.8157BUY299 4.65

4NSEAPOLLOPIPEEQ351.95351.95340350.8347.214361BUY4 339.566

5NSEASHOKLEYEQ52.152.355152.751212BUY7051.82

6NSEAUROPHARMAEQ789805.45775.35796.95782.4275BUY36 08.73

7NSEAXISBANKEQ426.5435.55408.7427.45413.55900BUY25 5575.45

8

9
Worksheet: 1-Sheet1 Problem 2

Macros
Here 2a
https://excelfox.com/forum/showthread.php/2577-Appendix-Thread-(-Codes-for-other-Threads-(-Avinash-)-)?p=14645&viewfull=1#post14645
Or
Here 2b
https://excelfox.com/forum/showthread.php/2577-Appendix-Thread-(-Codes-for-other-Threads-(-Avinash-)-)?p=14646&viewfull=1#post14646

[fixer]

Thnx Alot Doc Sir for helping me in solving this problem
Have a Awesome Day
Problem Solved


Problem 2 solution has minor issue but i corrected it



' Problem 2a with VBA arrays ' https://excelfox.com/forum/showthread.php/2577-Appendix-Thread-(-Codes-for-other-Threads-(-Avinash-)-)?p=14645&viewfull=1#post14645
Sub SelectOutRowsNotToBeThere_SelectTheRowsIWant() ' Problem 2 ' https://excelfox.com/forum/showthread.php/2582-delete-entire-row-by-vbA
Rem 1 Worksheet data info
Dim WbABC As Workbook, WsABC As Worksheet
Set WbABC = Workbooks.Open(ThisWorkbook.path & "\ABC.xls")
Set WsABC = WbABC.Worksheets.Item(1)
Dim WbDEF As Workbook, WsDEF As Worksheet
Set WbABC = Workbooks.Open(ThisWorkbook.path & "\DEF PROBLEM 2.xlsx") ' this line WbDEF will be there i corrected it
Set WsDEF = WbABC.Worksheets.Item(1)
Dim LrABC As Long, LrDEF As Long
Let LrABC = WsABC.Range("A" & WsABC.Rows.Count & "").End(xlUp).Row ' Dynamically getting the last row in worksheet referenced by WsABC
Let LrDEF = WsDEF.Range("B" & WsDEF.Rows.Count & "").End(xlUp).Row ' Dynamically getting the last row in worksheet referenced by WsDEF
'Dim arrIn() As Variant
' Let arrIn() = WsABC.Range("A1:K" & LrABC & "").Value2 ' Instead of this i will use Cells in the "magic code line"
Dim arrSrch() As Variant
Let arrSrch() = WsDEF.Range("B1:B" & LrDEF & "").Value2
Dim arrDta() As Variant
Let arrDta() = WsABC.Range("I1:I" & LrABC & "").Value2
Rem 2 get array of indicies for wanted rows
Dim Cnt As Long
Dim strRws As String: Let strRws = "1" ' The string for the indicies will always want the first header row
For Cnt = 2 To LrABC
Dim MtchRes As Variant
Let MtchRes = Application.Match(arrDta(Cnt, 1), arrSrch(), 0)
If IsError(MtchRes) Then Let strRws = strRws & " " & Cnt ' for no match, we want to not delete this row, so do not select it out In other words this code line collects the rows not wanted to be deleted = rows wanted
Next Cnt
Rem 3 Get the indicies in a vertical array, since the "magic code line" needs a vertical array
Dim Rws() As String: Let Rws() = Split(strRws, " ", -1, vbBinaryCompare) ' This gives us a 1 dimensional "horizontal" array ( starting at indicie 0 )
Dim RwsT() As Variant: ReDim RwsT(1 To UBound(Rws) + 1, 1 To 1) ' +1 is needed because the
For Cnt = 1 To UBound(Rws) + 1
Let RwsT(Cnt, 1) = Rws(Cnt - 1)
Next Cnt
Rem 4 get the output array from "magic code line" :
Dim Clms() As Variant
Let Clms() = Evaluate("=Column(A:K)") ' columns 1 2 3 m4 5 6 7 8 9 10 11
Dim arrOut() As Variant
Let arrOut() = Application.Index(WsABC.Cells, RwsT(), Clms()) ' Magic code line http://www.eileenslounge.com/viewtopic.php?p=265384#p265384 http://www.eileenslounge.com/viewtopic.php?p=265384&sid=39b6d764de41f462fe66f62816e5d789#p265384 See also https://www.excelforum.com/excel-new-users-basics/1099995-application-index-with-look-up-rows-and-columns-arguments-as-vba-arrays.html#post4571172 , http://www.excelfox.com/forum/showthread.php/2145-Excel-VBA-Interception-and-Implicit-Intersection-and-VLookUp for full explanation
Rem 5 replace worksheet data with modified data arrayOut
WsABC.Cells.ClearContents
Let WsABC.Range("A1").Resize(UBound(arrOut(), 1), 11).Value2 = arrOut() ' We can paste in one go the contents of an arrasy to a worksheet range
Rem Close save workbooks
WbABC.Close Savechanges:=True ' Save the file and close it
WbDEF.Close ' Close file. No changes were made
End Sub

















































' Conventional macro for comparison: https://excelfox.com/forum/showthread.php/2577-Appendix-Thread-(-Codes-for-other-Threads-(-Avinash-)-)?p=14646&viewfull=1#post14646

[DocAElstein]

correct, I made the same mistake in both macros.
I corrected it just now in both macros.
( https://excelfox.com/forum/showthread.php/2577-Appendix-Thread-(-Codes-for-other-Threads-(-Avinash-)-)?p=14645&viewfull=1#post14645
https://excelfox.com/forum/showthread.php/2577-Appendix-Thread-(-Codes-for-other-Threads-(-Avinash-)-)?p=14646&viewfull=1#post14646 )
Well spotted!

[fixer]

No Problem Doc Sir, it was a minor error
Thnx For the Help

[fixer]

If column C has blank cell then delete that entire row

Hi Experts,
I am looking for a macro that will do the things mentioed below

There are two different files(file name book1.xlsx & book2.xlsx)
If column C has a blank cell then delete that entire row by vba in both the file(check in both the file one by one & if they have a blank cell in column C then delete that entire row by vba in both files)
both files may be located at any place in the pc so the path should be hardcoded in the macro(so i can change it as per my needs)

Thnx For the Help

[fixer]

No Problem Doc Sir, it was a minor error
Thnx For the Help

[DocAElstein]

_ Are both files located at the same place? ( Can be anywhere, but is it the same place for both files or a different place?)
_ And is it always only one worksheet, the first one for every file?

[DocAElstein]

_ Are both files located at the same place? ( Can be anywhere, but is it the same place for both files or a different place?)
_ And is it always only one worksheet, the first one for every file?

[fixer]

No Doc Sir, both files are not located at same place

yes the first sheet Doc Sir

[DocAElstein]

OK, I will post again when I have the solution for you

[DocAElstein]

I will take a guess that you want to open the files.
I am assuming that columns always go to column U. If that is not the case, then you need to make the last column dynamic if the columns used can vary for different files. ( If you need to do that, and can’t figure out how, then I will do that for you later, probably tomorrow )

I have used the same ways as I did in the last macro for you, ( to solve Problem 2 https://excelfox.com/forum/showthread.php/2364-Delete-rows-based-on-match-criteria-in-two-excel-files?p=14648&viewfull=1#post14648 )

I do not delete any rows
Instead I collect indices of the rows you want to have = rows which are not to be deleted. For you test data, the rows you want are 2 3 and 4
Finally the macro replaces the original range with a new one containing just the rows you want

Before

_____ Workbook: Book2.xlsx ( Using Excel 2007 32 bit )
Row\Col
A
B
C
D
E
F
G
H
I
J
K
L
M
N
O
P
Q
R
S
T
U

1


2NSEEQBAJAJNANANA
0
12
0SELLMARKETNACLIMISDAYWC5758NA
3NA


3NSEEQAMARAJABATNANANA
0
12
0SELLMARKETNACLIMISDAYWC5758NA
3NA


4NSEEQASIANPAINTNANANA
0
12
0SELLMARKETNACLIMISDAYWC5758NA
3NA
Worksheet: Sheet1

_____ Workbook: Book1.xlsx ( Using Excel 2007 32 bit )
Row\Col
A
B
C
D
E
F
G
H
I
J
K
L
M
N
O
P
Q
R
S
T
U

1


2NSEEQADANIPOWERNANANA
0
12
0SELLMARKETNACLIMISDAYWC5758NA
3NA


3NSEEQAMARAJABATNANANA
0
12
0SELLMARKETNACLIMISDAYWC5758NA
3NA


4NSEEQASIANPAINTNANANA
0
12
0SELLMARKETNACLIMISDAYWC5758NA
3NA
Worksheet: Sheet1


After

_____ Workbook: Book2.xlsx ( Using Excel 2007 32 bit )
Row\Col
A
B
C
D
E
F
G
H
I
J
K
L
M
N
O
P
Q
R
S
T
U

1NSEEQBAJAJNANANA
0
12
0SELLMARKETNACLIMISDAYWC5758NA
3NA


2NSEEQAMARAJABATNANANA
0
12
0SELLMARKETNACLIMISDAYWC5758NA
3NA


3NSEEQASIANPAINTNANANA
0
12
0SELLMARKETNACLIMISDAYWC5758NA
3NA


4
Worksheet: Sheet1

_____ Workbook: Book1.xlsx ( Using Excel 2007 32 bit )
Row\Col
A
B
C
D
E
F
G
H
I
J
K
L
M
N
O
P
Q
R
S
T
U

1NSEEQADANIPOWERNANANA
0
12
0SELLMARKETNACLIMISDAYWC5758NA
3NA


2NSEEQAMARAJABATNANANA
0
12
0SELLMARKETNACLIMISDAYWC5758NA
3NA


3NSEEQASIANPAINTNANANA
0
12
0SELLMARKETNACLIMISDAYWC5758NA
3NA


4
Worksheet: Sheet1

Macro here https://excelfox.com/forum/showthread.php/2577-Appendix-Thread-(-Codes-for-other-Threads-(-Avinash-)-)?p=14657&viewfull=1#post14657



Note: You must change this line

Let arrWbs() = Array(ThisWorkbook.path & "\Book1.xlsx", ThisWorkbook.path & "\Book2.xlsx") ' - CHANGE TO SUIT

To something like this

Let arrWbs() = Array("C:\Users\WolfieeeStyle\Book1.xlsx", "C:\Users\WolfieeeStyle\Desktop\Book2.xlsx", "C:\Users\Desktop\MyBook.xlsx") '

[fixer]

Doc Sir the data can be more than in column U, there is a chance the data will be till column Z or more than that

[fixer]

I am trying to make the last column Dynamic & if I unable share the details then plz look at it tomorrow & help me for the same
Thnx Alot Doc Sir for helping me in solving the same
Have a Awesome Day

[fixer]

I am trying to make the last column Dynamic & if I unable share the details then plz look at it tomorrow & help me for the same
Thnx Alot Doc Sir for helping me in solving the same
Have a Awesome Day

[DocAElstein]

Hi

Making the Last column , Lc, dynamic in your last macro , Sub OnlyHaveRowsWhereColumnCisNotEmpty()

In this case, the usual way we did it before, something like
Ws.Cells.Item(1, Ws.Columns.Count).End(XlToLeft).Column
or like
Ws.Cells.Item(3, Ws.Columns.Count).End(XlToLeft).Column
will not work sometimes
it will not work if , in those examples , row 1 or row 3 was empty, which, based on your test data, might have been the case.
( I am assuming in all this work that rows other than row 1 might have an empty cell in column C, and , like your test data , an entire row might be empty…… )

We can try something else
We can use the UsedRange property's available properties of
the start column, UsedRange.Column
and the columns count UsedRange.Columns.Count
Using
UsedRange.Columns.Count- UsedRange.Column + 1

That is half the solution to the dynamic column problem. It returns us the last column number, which for yout example files , should be 21 ( Column Letter U )

The next part of this problem is that my macro, Sub OnlyHaveRowsWhereColumnCisNotEmpty() , needs to use the column Letter , not the column number.

You need to include this function , ( which you have had this function already from me many times before )

' https://excelfox.com/forum/showthread.php/1546-TESTING-Column-Letter-test-Sort-Last-Row?p=7214#post7214
Public Function CL(ByVal lclm As Long) As String ' http://www.excelforum.com/development-testing-forum/1101544-thread-post-appendix-no-reply-needed-please-do-not-delete-thanks-4.html#post4213980
Do: Let CL = Chr(65 + (((lclm - 1) Mod 26))) & CL: Let lclm = (lclm - (1)) \ 26: Loop While lclm > 0
End Function


That function must
Either
Go in the same code module as Sub OnlyHaveRowsWhereColumnCisNotEmpty[color=lightgreyDynamicColumns[/color]()
Or
It can go in any other module: Just to illustrate that point I have uploaded two sample workbooks,
macro1.xlsm has the function and the next macro version in it in the same code module.
Macro2.xlsm has them in separate modules, like this:
MainMacroAndFunctionCodeModules.JPG : https://imgur.com/siBZVuz
3312


The two changes for the dynamic column is
_1 a new line
Dim Lc As Long: Let Lc = Ws.UsedRange.Columns.Count - Ws.UsedRange.Column + 1
_2 Modify the column indicia code line, Clms() = Evaluate("=Column(A:U)")
Clms() = Evaluate("=Column(A:" & CL(Lc) & ")")
_3 You need to include the function CL( )



The next macro version and function, Sub OnlyHaveRowsWhereColumnCisNotEmptyDynamicColumns() , is here:
https://excelfox.com/forum/showthread.php/2577-Appendix-Thread-(-Codes-for-other-Threads-(-Avinash-)-)?p=14663&viewfull=1#post14663
https://excelfox.com/forum/showthread.php/2577-Appendix-Thread-(-Codes-for-other-Threads-(-Avinash-)-)/page29#post14663
and also in the files:
macro1.xlsm : https://app.box.com/s/tl3rs9693jwuv9c2w36ok8fpaewuf0ta
macro2.xlsm : https://app.box.com/s/t35238lm19bj6y0p6m6p68uaknsdf37z







Alan

[fixer]

Thnx Alot Doc Sir for helping me in solving this problem
Problem Solved
Have a Awesome Day Bro

Only a small thing is missing, i want to save the changes also but i am unable to make it
i mentioned wb.save but it is not successful, So i need help regarding the same

[DocAElstein]

What exactly did you try? You must take your time and explain everything more carefully, or you waste my time with lots of unecerssary replies...
I want to save the output sir
the macro is runing perfect but in the last we have to mention lines that will save the changes

You did not answer my question

What exactly did you try? You must take your time and explain everything more carefully, or you waste my time with lots of unnecessary replies...

[fixer]

Sub STEP8()
Dim arrWbs() As Variant
Let arrWbs() = Array("C:\Users\WolfieeeStyle\Desktop\A.xlsx", "C:\Users\WolfieeeStyle\Desktop\Files\B.xlsx")

Dim Wb As Workbook, Ws As Worksheet

Dim Stear As Variant
For Each Stear In arrWbs()
' 2a Worksheets data info
Set Wb = Workbooks.Open(Stear)

Set Ws = Wb.Worksheets.Item(1)
Dim LrC As Long: Let LrC = Ws.Range("C" & Ws.Rows.Count & "").End(xlUp).Row
Dim Lc As Long: Let Lc = Ws.UsedRange.Columns.Count - Ws.UsedRange.Column + 1
Dim arrC() As Variant: Let arrC() = Ws.Range("C1:C" & LrC & "").Value2

Dim Cnt As Long
For Cnt = 1 To LrC
Dim strRws As String
If arrC(Cnt, 1) <> "" Then Let strRws = strRws & Cnt & " "
Next Cnt
Let strRws = Left(strRws, Len(strRws) - 1)

Dim Rws() As String: Let Rws() = Split(strRws, " ", -1, vbBinaryCompare)
Dim RwsT() As Variant: ReDim RwsT(1 To UBound(Rws) + 1, 1 To 1)
For Cnt = 1 To UBound(Rws) + 1
Let RwsT(Cnt, 1) = Rws(Cnt - 1)
Next Cnt

Dim Clms() As Variant
'
Let Clms() = Evaluate("=Column(A:" & CL(Lc) & ")")
Dim arrOut() As Variant
Let arrOut() = Application.Index(Ws.Cells, RwsT(), Clms())

Ws.Cells.ClearContents
Let Ws.Range("A1").Resize(UBound(arrOut(), 1), 21).Value2 = arrOut()

Let strRws = ""
Next Stear

arrWbs.Save 'Here we have to mention lineswhich will save the changes done by the macro, I tried by wb.save also & arrWbs.Save also but it was not correct
arrWbs.Close 'Here we have to mention lineswhich will close all the files opened by macro


End Sub


Public Function CL(ByVal lclm As Long) As String
Do: Let CL = Chr(65 + (((lclm - 1) Mod 26))) & CL: Let lclm = (lclm - (1)) \ 26: Loop While lclm > 0
End Function








Plz see the code & my remarks
I have mentioned the problem Doc Sir in the remarks

[DocAElstein]

OK, you seem to be trying to save all the files at once. That is not so easy to do.

arrWbs() is just text. Inside it, it just looks like this:
" C:\Users\WolfieeeStyle\Desktop\A.xlsx _ , _ C:\Users\WolfieeeStyle\Desktop\Files\B.xlsx "

You can’t apply any property action command to a piece of text


In my macro, each time the loop is done, the Workbook object, Wb is made from the string path and name of a workbook, ( which is held each time in the variable Stear ).

You know how to save and close a single Workbook with like
Wb.Save
Wb.Close

So you need to have those code lines inside the loop which is done For every workbook

So then Each time it will save Each workbook


Wb.Save
Wb.Close
Next Stear
End Sub


Example in this file:

Macro2.xlsm : https://app.box.com/s/t35238lm19bj6y0p6m6p68uaknsdf37z



That’s all I have time for today.
Before you post any questions again, think more carefully , and read what I write…

[fixer]

http://eileenslounge.com/viewtopic.php?f=30&t=35025

[fixer]

Problem Solved
Thnx Alot Doc Sir For helping me in solving this Problem Sir
Have a Great Day

[fixer]

Hi Experts,


Sub STEP6()
Dim Wb1 As Workbook, Wb2 As Workbook
Dim Ws1 As Worksheet, Ws2 As Worksheet
Dim Lr1 As Long, Lr2 As Long: Let Lr1 = 5000: Lr2 = 5000
Set Wb1 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\1.xls")
Set Ws1 = Wb1.Worksheets(1)
Set Wb2 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\Files\Error.xlsx")
Set Ws2 = Wb2.Worksheets(1)

Dim rngSrch As Range: Set rngSrch = Ws2.Range("C1:C" & Lr2 & "")
Dim rngDta As Range: Set rngDta = Ws1.Range("B2:B" & Lr1 & "")

Dim Cnt As Long
For Cnt = Lr2 To 1 Step -1
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
rngDta.Rows(Cnt).EntireRow.Delete Shift:=xlUp
Else
End If

Next Cnt
Wb1.Close SaveChanges:=True
Wb2.Close SaveChanges:=True
End Sub





This code has limitation of LR1=5000 & LR2= 5000(plz remove the limitations of this macro)
& one more issue is there with this macro
I am sending the sample file plz run the macro & see the output
If error.xlsx is blank sheet then it is giving something different output plz see

[mail]

https://eileenslounge.com/viewtopic.php?f=30&t=35013&p=271877#p271877
https://eileenslounge.com/viewtopic.php?f=30&t=35042

[mail]

.

[DocAElstein]

This code has limitation of LR1=5000 & LR2= 5000(plz remove the limitations of this macro)
Avinash,
Your apparent ability or unwillingness to understand or remember anything is now at such a high level that
Either
_1) You are trying deliberately as hard as possible to annoy us
or
_2) If _1) is not correct, then you really should now see a Doctor or Psychologist, since you are very likely seriously mentally Ill

We have had the making last row dynamic issue over and over again. Even just recently almost an identical question from you was answered, and the macro was also almost identical!!!:
https://eileenslounge.com/viewtopic.php?f=30&t=34937
https://eileenslounge.com/viewtopic.php?p=271316#p271316
https://excelfox.com/forum/showthread.php/2364-Delete-rows-based-on-match-criteria-in-two-excel-files?p=14565&viewfull=1#post14565


Edit: And you posted this same question a few days before.. Bro you are in a total mixed up mess!!!
(https://excelfox.com/forum/showthread.php/2584-Avinash-Crap-Pending-sorting-out?p=14599&viewfull=1#post14599 )






one more issue is there with this macro
I am sending the sample file plz run the macro & see the output
If error.xlsx is blank sheet then it is giving something different output plz see
I have no idea what you are asking. I have no idea what it is that you are trying to say .
Possibly it is something to do with problem 2 here:
https://excelfox.com/forum/showthread.php/2364-Delete-rows-based-on-match-criteria-in-two-excel-files?p=14565&viewfull=1#post14565
The problem is your usual incompetence. You mix everything up, and half the time either don’t know what you want or cannot explain what you want. This is not a VBA issue. This is a problem with your brain that needs medical help.

Alan


Edit.. and here we go yet once again .... same question
https://eileenslounge.com/viewtopic.php?p=272085#p272085
_.. same wrong answer...
https://eileenslounge.com/viewtopic.php?p=272094#p272094
( wrong caslculation of Lr2 )
_... and yet agin you think its right ... and another set of duplicated cross postings will begin when you realise yet agin that it is wrong... Bro - you need medical help, fast! NO JOKE!

[fixer]

Hi Experts,
I want to make this macro Lr as dynamic


Sub STEP4()
Dim Wb1 As Workbook, Wb2 As Workbook
Set Wb1 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\HotStocks\1.xls")
Set Wb2 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\HotStocks\AlertCode s.xlsx")
Dim Ws1 As Worksheet, WS2 As Worksheet
Set Ws1 = Wb1.Worksheets.Item(1)
Set WS2 = Wb2.Worksheets.Item(4)
Dim Lr1 As Long, Lr2 As Long: Let Lr1 = 5000: Lr2 = 5000
Dim rngSrch As Range: Set rngSrch = WS2.Range("B1:B" & Lr2 & "")
Dim rngDta As Range: Set rngDta = Ws1.Range("I2:I" & Lr1 & "")

Dim Cnt As Long
For Cnt = Lr2 To 1 Step -1
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
rngDta.Rows(Cnt).EntireRow.Delete Shift:=xlUp
Else
End If

Next Cnt
Wb1.Close SaveChanges:=True
Wb2.Close SaveChanges:=True
End Sub







Thnx For the Help





Sub STEP5()
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\HotStocks\H2.xlsb")
Dim Ws1 As Worksheet, Ws2 As Worksheet
Set Ws1 = Wb1.Worksheets.Item(1)
Set Ws2 = Wb2.Worksheets.Item(2)
Dim Lr1 As Long, Lr2 As Long:
Let Lr1 = Ws1.Range("A" & Ws1.Rows.Count & "").End(xlUp).Row
Let Lr2 = Ws1.Range("A" & Ws1.Rows.Count & "").End(xlUp).Row
Dim rngSrch As Range: Set rngSrch = Ws2.Range("A2:A" & Lr2 & "")
Dim rngDta As Range: Set rngDta = Ws1.Range("B2:B" & Lr1 & "")

Dim Cnt As Long
For Cnt = Lr2 To 1 Step -1
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

Else
rngDta.Rows(Cnt).EntireRow.Delete Shift:=xlUp
End If

Next Cnt
Wb1.Close SaveChanges:=True
Wb2.Close SaveChanges:=True
End Sub


I changed the lr for this problem Plz see is it perfect?
Both macros are givng perfect Result But u have recommended it will work sometimes & may be sometimes it will not work thats y i posted this question






Sub STEP6()
Dim Wbm As Workbook: Set Wbm = ThisWorkbook
Dim Wb1 As Workbook, Wb2 As Workbook
Dim Ws1 As Worksheet, Ws2 As Worksheet
Dim Lr1 As Long, Lr2 As Long: Let Lr1 = 5000: Lr2 = 5000
Set Wb2 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\WolfieeeStyle\9.15\ Files\Error.xlsx")
Set Ws2 = Wb2.Worksheets.Item(1)
Set Wb1 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\1.xls")
Set Ws1 = Wb1.Worksheets.Item(1)

Dim rngSrch As Range: Set rngSrch = Ws2.Range("C1:C" & Lr2 & "")
Dim rngDta As Range: Set rngDta = Ws1.Range("B2:B" & Lr1 & "")
Dim Cnt As Long
For Cnt = Lr2 To 1 Step -1
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
rngDta.Rows(Cnt).EntireRow.Delete Shift:=xlUp
Else
End If

Next Cnt
Wb1.Close SaveChanges:=True
Wb2.Close SaveChanges:=True
End Sub





Sub STEP9()
Dim Wb1 As Workbook, Wb2 As Workbook
Dim Ws1 As Worksheet, Ws2 As Worksheet

Set Wb1 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\1.xls")
Set Ws1 = Wb1.Worksheets(1)
Set Wb2 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\WolfieeeStyle\9.15\ Files\Error.xlsx")
Set Ws2 = Wb2.Worksheets(1)
Dim Lr1 As Long, Lr2 As Long
Let Lr1 = Ws1.Range("B" & Ws1.Rows.Count).End(xlUp).Row
Let Lr2 = Ws1.Range("B" & Ws1.Rows.Count).End(xlUp).Row

Dim rngSrch As Range: Set rngSrch = Ws2.Range("C1:C" & Lr2 & "")
Dim rngDta As Range: Set rngDta = Ws1.Range("B2:B" & Lr1 & "")

Dim Cnt As Long
If ActiveSheet.Cells(1, 1) = "" Then
Wb1.Close SaveChanges:=False
Wb2.Close SaveChanges:=False
Exit Sub
End If
For Cnt = Lr2 To 1 Step -1
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
rngDta.Rows(Cnt).EntireRow.Delete Shift:=xlUp
Else
End If

Next Cnt
Wb1.Close SaveChanges:=True
Wb2.Close SaveChanges:=True
End Sub

[DocAElstein]

This is crazy!! I have told you so many times!!!

Lr1 is for Ws1
Lr2 is for Ws2

Lr1 = Ws1.Range("B" & Ws1.Rows.Count).End(xlUp).Row
Lr2 = Ws2.Range("B" & Ws2.Rows.Count).End(xlUp).Row



If a macro like those originated from me, which it may well have, then the basic idea , originally, would have been this:
A row is possibly being deleted in a fairly bog standard conventional worksheet code line in a Loop. Hence it must Loop backwards… Based on the initial given requirement, the macro was doing this: A column of data in a worksheet is gone through/looped. That column would be the rngDta. We can see that in the Loop since at each Loop as the Loop count variable, Cnt , changes, we have the next back/down Item in the rngDta as What:= is being looked for rngDta.Item(Cnt)
In these macros , it seems that the data is in Ws1 , and the rngDta from a dynamic last row, is given by something of the form
Ws1.Range("B" & Ws1.Rows.Count).End(xlUp).Row
That is the data that is gone through/Looped , so the Looping should be done like
__For Lr1 To 1 Step -1
The range in which each data item is looked for is rngSrch , which would appear in this macro to be in Ws2 and correspondingly , originally we would have dynamically obtained that range something like
Ws2.Range("C1:C" & Lr2 & "")
With our dynamically obtained last row being obtained something like this
Ws2.Range("B" & Ws2.Rows.Count).End(xlUp).Row
Based on if a match is or is not found, then something is or isn’t done.



It is starting to give the impression that you have no access to, or knowledge what so ever of Excel. let alone VBA.
You often don't / won't understand something that a small mentally handicapped child could and mostly would understand.
It is clear that there is no real inteligence behind who or what is producing the questions: most of the time you have no idea at all about the questions you are pasting.
You seem to be pasting badly explained mixed up questions , and as before, posting some canned replies that you don’t seem to understand either yourself half the time.

Whatever it is you are attempting to do , it is incredibly inefficient, and mostly achieves nothing other than just wasting every bodies time.

The only amazing thing is that you are getting worse and worse. Whatever you are attempting was, up until now, wasting 10 – 100 times more time than necessary. Your post are now becoming so full of errors and so mixed up that you are wasting 1000 times more than necessary.
It is totally crazy, and getting crazier by the day… .

But some people at excelforum seem happy to play the game with you. So maybe best is to just register another dozen accounts there and continue in your games.

I am beginning to think that your main aim is to just keep as many people possible busy at a forum answering the same and similar questions over and over again… I think there is at least a small chance that you are a Bot are part of an attempt to develop a Bot to ask questions at forums..
It is clear that there is no real inteligence behind who or what is producing the questions: most of the time you have no idea at all yourself about the questions you are pasting, that is to say duplicate cross posting with multiple accounts everywhere...( https://excelfox.com/forum/showthread.php/2278-Misc-Leonardo1234-rider-1234-vixer-Highlighting-Simple-Early-stuff-Avinash-Introduction )

[fixer]

Np
i changed all these lr to : Let Lr1 = 10000: Lr2 = 10000
Simple, I was just trying to make it perfect but No Doubt it will also work & it will work 101% perfect
I asked the problem bcoz on that day u told sometime it will work or sometime it will not work thats y i asked the question but by putting this line : Let Lr1 = 5000: Lr2 = 5000
it will work perfect bcoz u have created this macro & u have putted : Let Lr1 = 5000: Lr2 = 5000 this line to the macro & and i was not satisfied with this line : Let Lr1 = 5000: Lr2 = 5000 , but now i will manage it

I have no more question to ask 99.99%, work is done Bro

Thnx Doc for helping me for providing me the macros & for putting ur Great Effort in my Research
Have a Awesome Day

[fixer]

Sub ApplciationProgram()
Application.ScreenUpdating = False
Dim wb1 As Workbook
Dim wb2 As Workbook
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim RowCount1 As Integer
Dim ColumnCount1 As Integer
Dim RowCount2 As Integer
Dim ColumnCount2 As Integer
Dim OnemyArray() As Variant
Dim TwomyArray() As Variant
Dim RowCount As Integer
Dim sheetNumber As Integer
Dim rowNumber As Integer
Dim stateFlag As Boolean
Dim RowNumbers() As Variant
Dim Counter As Integer
stateFlag = False
While (Not stateFlag)
TwoExcellFilePath = "C:\Users\WolfieeeStyle\Desktop\HotStocks\AlertCode s.xlsx"
sheetNumber = 4
Set wb1 = ActiveWorkbook
Set ws1 = wb1.Sheets(3)
RowCount1 = ws1.UsedRange.Rows.Count
ColumnCount1 = ws1.UsedRange.Columns.Count
OnemyArray = ws1.Range("A1:H" & RowCount1).Value


Set wb2 = Workbooks.Open(filename:=TwoExcellFilePath)
Set ws2 = wb2.Sheets(sheetNumber)
RowCount2 = ws2.UsedRange.Rows.Count
ColumnCount2 = ws2.UsedRange.Columns.Count
RowCount = RowCount2
TwomyArray = ws2.Range("A1:K" & RowCount2).Value
wb2.Close SaveChanges:=True
Set wb2 = Nothing
Counter = 0
ReDim RowNumbers(RowCount1)
For i = 2 To RowCount1
For j = 1 To RowCount2
If (ws1.Cells(i, 5) = TwomyArray(j, 2) And ws1.Cells(i, 3) = TwomyArray(j, 4)) Then
ws1.Rows(i).EntireRow.Delete
RowCount1 = ws1.UsedRange.Rows.Count
End If
Next
Next
wb1.Save
Set wb1 = Nothing
Exit Sub

Wend
Application.ScreenUpdating = True
Exit Sub

End Sub


Function copyFiles(source As String, destination As String)
Dim fso As Object
Dim strFileExists As String
Set fso = CreateObject("scripting.filesystemobject")
strFileExists = Dir(source)
If strFileExists > "" Then
fso.movefile source:=source, destination:=destination
Else
MsgBox "File does not exists to copy"
End If
End Function
Function SplitWord(text As String) As String
Dim indexPoint As Integer
indexPoint = InStr(text, "-EQ")
SplitWord = Left(text, indexPoint - 1)
End Function



I have a macro to do the same but it is not deleting the entire row so plz see & help me out in the same
https://www.excelforum.com/excel-programming-vba-macros/1326747-delete-entire-row-by-vba-macro-correction.html

[fixer]

Problem Solved

[Logit]

.
For future readers to learn ... how did you correct the issue ?

[fixer]

Yes this problem is solved in excel forum
https://www.excelforum.com/excel-programming-vba-macros/1326747-delete-entire-row-by-vba-macro-correction.html

I shared the link

[DocAElstein]

“Moderator” Notice

**I am Banning you to prevent you making any more postings here of the type you have been making here and elsewhere under hundreds of different user names at many of the English speaking Excel and Office help forums for the last couple of years.

The type of post that you have been posting suggest that
_ You may be one person or a !!team of many people working at something organised like a Call Centre.
( !! Sometime when you have been “caught” cross posting, you did not know yourself where you cross posted, and asked to be told. ( Or you maybe only wanted to admit to those where you got “caught”) )
_ You have almost no understanding of the English language
_ You may not have a computer and may have no access to Excel
_ You have no interest in Excel or Excel VBA
_ You have almost no knowledge or interest in any of the questions that you are asking
_ You may be simply offering a service of posting other peoples questions and supplying them with any answers you get.
_ You may be part of the development of a question asking and Replying Bot

_ In some cases, something extremely simple to understand, has been explained to you very many times, in great detail , even graphically, such that even a small mentally handicapped child could understand it and remember it. Despite this, you continually ask exactly that same question over and over again: If you are part of a team interested in only posting questions and taking the answer, then you are very badly organised,
Or
There is no real intelligence behind what is producing your questions and posts
_ One of the things you consistently do after receiving a macro is to delete all explanations, explaining 'comments and all files associated, and indeed it appears as if you try to remove almost all record of the coding and the question and answer. This further encourages the posting of the same or similar questions over and over again.

Whatever you are attempting to do, it appears to be extremely, almost insanely, inefficient ,
compared to
a single person with a computer and Excel, and a minimum of basic Excel VBA knowledge trying to achieve the same.

The main reason for the ban is
Whatever you are attempting to do, it is requiring 10-100 times more time than is typically required of helpers at a forum. All indications are that what you are doing will fail to achieve anything, and is therefore a total waste of everyone’s time. At excelfox, the current small number of helpers have only a limited amount of time, but even if we had more members, excelfox would not be the place for you.
I am totally bored shitless with your Threads that are almost always asking the same thing.
## Some of the major forums may be a good place for you to post. There are some senior brain dead morons there who are happy to keep answering the same questions over and over again. Half of them are probably either senile Dement or just plain stupid anyway, and they don’t remember from one day to the next.

These are some suggestions, from me, on how you should continue
_ If you intend to continue, regardless of any of my previous suggestions, in postings of the type as you have done in the past, then you should think about making some changes to your wording, introduce some new canned replies, possibly organise a new set of similar questions and post at the major forums, such as mrexcel.com, excelforum.com, ozgrid.com
_ If you wish to make a career out of posting questions and getting answers without having any real intentions of thinking about anything, then excelfox is not the forum for you to post in. Most of the smaller forums are not the place for you. The larger forums may be able to accommodate you, if you give at least some thought to making it not quite so obvious: Your distinguishing characteristic is that you have been making it much more obvious than others doing the same, do: Many people do the such. At least half the traffic at such forums originates from such. I have passed many people on to such forums and they are making a successful career based on passing on the work done for them by helpers at the major forums. Such is actually encouraged, all be it , not openly, at the major forums.
_ If you have not understood most of this Moderator Notice , then your first priority should be to improve on your English. Indeed, your apparent understanding and ability in communicating in English suggests that you will achieve nothing whatsoever and fail completely in anything at all involving communicating in English.

_ If you are, as you sometimes told me via PM, actively working on an important personal problem requiring VBA , then you are doing it totally wrongly: You have been on the project already for at least two years and have a mixed up set of codings produced by many different people. Some work . Some don’t. You have not the slightest idea or understanding of any of the codings. You will never be able to use them to any effect. You are getting an ever increasingly different set of codings with every post you make, and reply you get, which all just confuses the issues further. You are making negative progress, Bro! You are working and going backwards most of the time.
If , on the other hand, you had a computer, with Excel, and spent a few weeks learning VBA, and then carefully studied all the macros that you have been given, then you would be able to answer most of your further questions, and would have at least a chance of being able to use the codings effectively:-
1 Month learn VBA and 1 month getting answers, partly alone, partly with help from forums = Finished Success
2+ Years posting the same and similar questions and just taking the answers = Never Ending Fail
_ It is unlikely that the macros you have that work will ever be very efficient and will likely be slower than anyone else’s: They will certainly not be the best possible. Giving you better coding has proved to be impossible: It is not possible to pass on better codings because of the ridiculously inefficient way that you are organising whatever it is that you are doing: The person receiving and passing on the coding needs to understand the English language and to understand some basic coding and to understand how to use such better coding. We have tried this a few times, but it proved always completely impossible to do. One example of this is the issue of text files: Because you are mostly dealing with values, the use of text files is almost certainly beneficial and in some cases the only efficient way to proceed. You have completely missed the point on this: You have repeated much work to try to avoid using text files. The problem was, and will never be, the issue of text files themselves. The issue is your total inability or unwillingness to understand anything at all about them.

Another possibility is that you and/ or yous are simply severally mentally handicapped: If that is the case then, then I am , sincerely, sorry for you, but you have no hope of achieving anything with what you are doing at forums , apart from wasting a lot of other people’s time.


##The main purpose of the question section of excelfox is approximately the following:
_1. Promote and improve the understanding of Excel and Excel VBA.
_2. Help people who get stuck on a problem and/or help people who are unsure how to proceed in solving a problem using Excel and Excel VBA.

Your objectives??
I do not know what the true reason is behind your postings. I can’t believe anything you say is your purpose, since you have lied and contradicted yourself in the past. The only thing we know 100% for sure is that your posting types are not for any of the purposes for which the question section of excelfox is intended.
You have had the benefit of the doubt given to you now very many times. You have had lots of chances.
You may be able to continue at some of the major forums, where some people are happy to continue to spend time to answer similar questions from the same source.
I do not think you will get any more replies to the types of postings you have been making at excelfox or at any other of the smaller English speaking Forums. You are wasting your time making any such posts from now on.
**I am Banning you, not as any form of punishment, but purely as in the past , it has proven to be the only way to prevent you wasting yours and other peoples time with your postings.
I do wish you luck and success with what ever it is you are attempting to do. But you should not be doing it at excelfox.
If you are attempting the personal project that you have told me about via PM, then you are going about it in completely the wrong way.
If you are trying to make a career of posting other people’s questions and getting answers for them, then you should post mostly at the major forums and organise yourself better: At least have access to Excel on a computer and learn the basics of VBA. If you are trying to make a career of posting other people’s questions and getting answers for them, as many people do, then you have made the mistake of making it too obvious. Many of the senior helpers at the main forums prefer to think that they are helping people rather than doing their work for them. What they don’t know, does not hurt them. :)


I will leave all your posts in the main forum for a few weeks. Then I will move them all to the test forum. I will probably further merge them. Eventually I may delete them all.


Bro, whatever you are trying to do, its not working. Its never going to work. Its just wasting everybody’s time.
You need first to learn English
Then get a computer with Microsoft Office.
Then learn some basic Excel and Excel VBA
Then start again.
If you ever come to excelfox again you will have to prove to me that
_ You are a real person
_ You are genuinely attempting a personal project and need help
_ You have a computer with Microsoft Office / Excel installed and that you can, and do, use it, and that you understand basic VBA programming.


Ref
https://excelfox.com/forum/showthread.php/2278-Misc-Leonardo1234-rider-1234-vixer-Highlighting-Simple-Early-stuff-Avinash-Introduction

[DocAElstein]

“Moderator” Notice

**I am Banning you to prevent you making any more postings here of the type you have been making here and elsewhere under hundreds of different user names at many of the English speaking Excel and Office help forums for the last couple of years.

The type of post that you have been posting suggest that
_ You may be one person or a !!team of many people working at something organised like a Call Centre.
( !! Sometime when you have been “caught” cross posting, you did not know yourself where you cross posted, and asked to be told. ( Or you maybe only wanted to admit to those where you got “caught”) )
_ You have almost no understanding of the English language
_ You may not have a computer and may have no access to Excel
_ You have no interest in Excel or Excel VBA
_ You have almost no knowledge or interest in any of the questions that you are asking
_ You may be simply offering a service of posting other peoples questions and supplying them with any answers you get.
_ You may be part of the development of a question asking and Replying Bot

_ In some cases, something extremely simple to understand, has been explained to you very many times, in great detail , even graphically, such that even a small mentally handicapped child could understand it and remember it. Despite this, you continually ask exactly that same question over and over again: If you are part of a team interested in only posting questions and taking the answer, then you are very badly organised,
Or
There is no real intelligence behind what is producing your questions and posts
_ One of the things you consistently do after receiving a macro is to delete all explanations, explaining 'comments and all files associated, and indeed it appears as if you try to remove almost all record of the coding and the question and answer. This further encourages the posting of the same or similar questions over and over again.

Whatever you are attempting to do, it appears to be extremely, almost insanely, inefficient ,
compared to
a single person with a computer and Excel, and a minimum of basic Excel VBA knowledge trying to achieve the same.

The main reason for the ban is
Whatever you are attempting to do, it is requiring 10-100 times more time than is typically required of helpers at a forum. All indications are that what you are doing will fail to achieve anything, and is therefore a total waste of everyone’s time. At excelfox, the current small number of helpers have only a limited amount of time, but even if we had more members, excelfox would not be the place for you.
I am totally bored shitless with your Threads that are almost always asking the same thing.
## Some of the major forums may be a good place for you to post. There are some senior brain dead morons there who are happy to keep answering the same questions over and over again. Half of them are probably either senile Dement or just plain stupid anyway, and they don’t remember from one day to the next.

These are some suggestions, from me, on how you should continue
_ If you intend to continue, regardless of any of my previous suggestions, in postings of the type as you have done in the past, then you should think about making some changes to your wording, introduce some new canned replies, possibly organise a new set of similar questions and post at the major forums, such as mrexcel.com, excelforum.com, ozgrid.com
_ If you wish to make a career out of posting questions and getting answers without having any real intentions of thinking about anything, then excelfox is not the forum for you to post in. Most of the smaller forums are not the place for you. The larger forums may be able to accommodate you, if you give at least some thought to making it not quite so obvious: Your distinguishing characteristic is that you have been making it much more obvious than others doing the same, do: Many people do the such. At least half the traffic at such forums originates from such. I have passed many people on to such forums and they are making a successful career based on passing on the work done for them by helpers at the major forums. Such is actually encouraged, all be it , not openly, at the major forums.
_ If you have not understood most of this Moderator Notice , then your first priority should be to improve on your English. Indeed, your apparent understanding and ability in communicating in English suggests that you will achieve nothing whatsoever and fail completely in anything at all involving communicating in English.

_ If you are, as you sometimes told me via PM, actively working on an important personal problem requiring VBA , then you are doing it totally wrongly: You have been on the project already for at least two years and have a mixed up set of codings produced by many different people. Some work . Some don’t. You have not the slightest idea or understanding of any of the codings. You will never be able to use them to any effect. You are getting an ever increasingly different set of codings with every post you make, and reply you get, which all just confuses the issues further. You are making negative progress, Bro! You are working and going backwards most of the time.
If , on the other hand, you had a computer, with Excel, and spent a few weeks learning VBA, and then carefully studied all the macros that you have been given, then you would be able to answer most of your further questions, and would have at least a chance of being able to use the codings effectively:-
1 Month learn VBA and 1 month getting answers, partly alone, partly with help from forums = Finished Success
2+ Years posting the same and similar questions and just taking the answers = Never Ending Fail
_ It is unlikely that the macros you have that work will ever be very efficient and will likely be slower than anyone else’s: They will certainly not be the best possible. Giving you better coding has proved to be impossible: It is not possible to pass on better codings because of the ridiculously inefficient way that you are organising whatever it is that you are doing: The person receiving and passing on the coding needs to understand the English language and to understand some basic coding and to understand how to use such better coding. We have tried this a few times, but it proved always completely impossible to do. One example of this is the issue of text files: Because you are mostly dealing with values, the use of text files is almost certainly beneficial and in some cases the only efficient way to proceed. You have completely missed the point on this: You have repeated much work to try to avoid using text files. The problem was, and will never be, the issue of text files themselves. The issue is your total inability or unwillingness to understand anything at all about them.

Another possibility is that you and/ or yous are simply severally mentally handicapped: If that is the case then, then I am , sincerely, sorry for you, but you have no hope of achieving anything with what you are doing at forums , apart from wasting a lot of other people’s time.


##The main purpose of the question section of excelfox is approximately the following:
_1. Promote and improve the understanding of Excel and Excel VBA.
_2. Help people who get stuck on a problem and/or help people who are unsure how to proceed in solving a problem using Excel and Excel VBA.

Your objectives??
I do not know what the true reason is behind your postings. I can’t believe anything you say is your purpose, since you have lied and contradicted yourself in the past. The only thing we know 100% for sure is that your posting types are not for any of the purposes for which the question section of excelfox is intended.
You have had the benefit of the doubt given to you now very many times. You have had lots of chances.
You may be able to continue at some of the major forums, where some people are happy to continue to spend time to answer similar questions from the same source.
I do not think you will get any more replies to the types of postings you have been making at excelfox or at any other of the smaller English speaking Forums. You are wasting your time making any such posts from now on.
**I am Banning you, not as any form of punishment, but purely as in the past , it has proven to be the only way to prevent you wasting yours and other peoples time with your postings.
I do wish you luck and success with what ever it is you are attempting to do. But you should not be doing it at excelfox.
If you are attempting the personal project that you have told me about via PM, then you are going about it in completely the wrong way.
If you are trying to make a career of posting other people’s questions and getting answers for them, then you should post mostly at the major forums and organise yourself better: At least have access to Excel on a computer and learn the basics of VBA. If you are trying to make a career of posting other people’s questions and getting answers for them, as many people do, then you have made the mistake of making it too obvious. Many of the senior helpers at the main forums prefer to think that they are helping people rather than doing their work for them. What they don’t know, does not hurt them. :)


I will leave all your posts in the main forum for a few weeks. Then I will move them all to the test forum. I will probably further merge them. Eventually I may delete them all.


Bro, whatever you are trying to do, its not working. Its never going to work. Its just wasting everybody’s time.
You need first to learn English
Then get a computer with Microsoft Office.
Then learn some basic Excel and Excel VBA
Then start again.
If you ever come to excelfox again you will have to prove to me that
_ You are a real person
_ You are genuinely attempting a personal project and need help
_ You have a computer with Microsoft Office / Excel installed and that you can, and do, use it, and that you understand basic VBA programming.


Ref
https://excelfox.com/forum/showthread.php/2278-Misc-Leonardo1234-rider-1234-vixer-Highlighting-Simple-Early-stuff-Avinash-Introduction