Log In
Register
Home
New Member
September 1, 2019
Offline
I will place the vba code in sanju.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 sanju.xlsx matches with cells of column B of sanju1.xlsx then delete the entire row of sanju1.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
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
Can you provide sample files, so we can see what values are in column C? These should be full matches, partial matches, case sensitive or not case sensitive?
New Member
September 1, 2019
Offline
Sure Sir
1.xls is sanju1.xlsx
and sanju.xlsx is basketorder.xlsx
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
You can put the code below in the BasketOrder.xlsm and run it, it should delete the rows from other files.
Sub DeleteRows()
Dim FSO As Object: Set FSO = CreateObject("scripting.filesystemobject")
Dim FileObj As Object, wb As Workbook, Cell As Range, DeleteRange As Range, Match As Variant
For Each FileObj In FSO.GetFolder(ThisWorkbook.Path).Files
If Not FileObj.Name Like ThisWorkbook.Name And Not FileObj.Name Like "~*" Then 'avoid this workbook and temp system files
Debug.Print FileObj.Name
Set wb = Workbooks.Open(FileObj.Path)
For Each Cell In wb.Worksheets(1).UsedRange.Columns(3).Cells
Match = Application.Match(Cell.Value, ThisWorkbook.Worksheets(1).UsedRange.Columns(2), 0)
If Not TypeName(Match) Like "Error" Then
If DeleteRange Is Nothing Then
Set DeleteRange = Cell
Else
Set DeleteRange = Union(DeleteRange, Cell)
End If
End If
Next
If Not DeleteRange Is Nothing Then DeleteRange.EntireRow.Delete
wb.Close True
End If
Next
Set FSO = Nothing
Set FileObj = Nothing
End Sub
1 Guest(s)
