Forum

Notifications
Clear all

Delete row by vba

4 Posts
2 Users
0 Reactions
93 Views
(@sanjana148)
Posts: 2
New Member
Topic starter
 

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

 
Posted : 01/09/2019 1:33 pm
(@catalinb)
Posts: 1937
Member Admin
 

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?

 
Posted : 04/09/2019 1:48 pm
(@sanjana148)
Posts: 2
New Member
Topic starter
 

Sure Sir

1.xls is sanju1.xlsx

and sanju.xlsx is basketorder.xlsx

 
Posted : 04/09/2019 2:00 pm
(@catalinb)
Posts: 1937
Member Admin
 

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

 
Posted : 06/09/2019 1:16 am
Share: