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

[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/showthrea...ll=1#post14645
https://excelfox.com/forum/showthrea...ll=1#post14646 )
Well spotted!

[fixer]

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

[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

[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/showthrea...ll=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
2	NSE	EQ	BAJAJ	NA	NA	NA	0	12	0	SELL	MARKET	NA	CLI	MIS	DAY	WC5758	NA			3	NA
3	NSE	EQ	AMARAJABAT	NA	NA	NA	0	12	0	SELL	MARKET	NA	CLI	MIS	DAY	WC5758	NA			3	NA
4	NSE	EQ	ASIANPAINT	NA	NA	NA	0	12	0	SELL	MARKET	NA	CLI	MIS	DAY	WC5758	NA			3	NA

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
2	NSE	EQ	ADANIPOWER	NA	NA	NA	0	12	0	SELL	MARKET	NA	CLI	MIS	DAY	WC5758	NA			3	NA
3	NSE	EQ	AMARAJABAT	NA	NA	NA	0	12	0	SELL	MARKET	NA	CLI	MIS	DAY	WC5758	NA			3	NA
4	NSE	EQ	ASIANPAINT	NA	NA	NA	0	12	0	SELL	MARKET	NA	CLI	MIS	DAY	WC5758	NA			3	NA

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
1	NSE	EQ	BAJAJ	NA	NA	NA	0	12	0	SELL	MARKET	NA	CLI	MIS	DAY	WC5758	NA			3	NA
2	NSE	EQ	AMARAJABAT	NA	NA	NA	0	12	0	SELL	MARKET	NA	CLI	MIS	DAY	WC5758	NA			3	NA
3	NSE	EQ	ASIANPAINT	NA	NA	NA	0	12	0	SELL	MARKET	NA	CLI	MIS	DAY	WC5758	NA			3	NA
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
1	NSE	EQ	ADANIPOWER	NA	NA	NA	0	12	0	SELL	MARKET	NA	CLI	MIS	DAY	WC5758	NA			3	NA
2	NSE	EQ	AMARAJABAT	NA	NA	NA	0	12	0	SELL	MARKET	NA	CLI	MIS	DAY	WC5758	NA			3	NA
3	NSE	EQ	ASIANPAINT	NA	NA	NA	0	12	0	SELL	MARKET	NA	CLI	MIS	DAY	WC5758	NA			3	NA
4

Worksheet: Sheet1

Macro here https://excelfox.com/forum/showthrea...ll=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")  '