PDA

View Full Version : Dlete row



rider@1234
08-31-2019, 07:33 AM
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 data.csv matches with cells of column B of cars.xlsx then delete the entire row of cars.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

mana
08-31-2019, 06:24 PM
Sub test()
Dim cars As Range
Dim csv As Range

Set cars = Workbooks.Open(ThisWorkbook.Path & "\cars.xlsx") _
.Sheets(1).Cells(1).CurrentRegion

Set csv = Workbooks.Open(ThisWorkbook.Path & "\data.csv") _
.Sheets(1).Cells(1).CurrentRegion.Columns("C")

cars.AdvancedFilter xlFilterInPlace, csv
cars.Offset(1).EntireRow.Delete
cars.Parent.ShowAllData

cars.Parent.Parent.Close True
csv.Parent.Parent.Close False

End Sub

rider@1234
08-31-2019, 08:36 PM
mana sir plz have a relook to the code its not working
i think u missed something in the code plz relook

mana
08-31-2019, 09:05 PM
Sub test2()
Dim cars As Range
Dim csv As Range

Set cars = Workbooks.Open(ThisWorkbook.Path & "\cars.xlsx") _
.Sheets(1).Cells(1).CurrentRegion

Set csv = Workbooks.Open(ThisWorkbook.Path & "\data.csv") _
.Sheets(1).Cells(1).CurrentRegion.Columns("C")

cars.Cells(2).Copy
csv.Cells(1).Insert xlDown
Set csv = csv.Offset(-1).Resize(csv.Cells.Count + 1)

cars.AdvancedFilter xlFilterInPlace, csv
cars.Offset(1).EntireRow.Delete
cars.Parent.ShowAllData

cars.Parent.Parent.Close True
csv.Parent.Parent.Close False

End Sub

rider@1234
08-31-2019, 09:24 PM
Si plz see the sample file this code is unable to do the same
I have attached the sampe file plz relook sir

mana
08-31-2019, 09:39 PM
Sub test2()
Dim cars As Range
Dim csv As Range

Set cars = Workbooks.Open(ThisWorkbook.Path & "\1.xls") _
.Sheets(1).Cells(1).CurrentRegion

Set csv = Workbooks.Open(ThisWorkbook.Path & "\BasketOrder..csv") _
.Sheets(1).Cells(1).CurrentRegion.Columns("C")

cars.Cells(2).Copy
csv.Cells(1).Insert xlDown
Set csv = csv.Offset(-1).Resize(csv.Cells.Count + 1)

cars.AdvancedFilter xlFilterInPlace, csv
cars.Offset(1).EntireRow.Delete
cars.Parent.ShowAllData

cars.Parent.Parent.Close True
csv.Parent.Parent.Close False

End Sub

rider@1234
08-31-2019, 09:44 PM
Thnx Alot Mana Sir for ur Great Support
Problem Solved

rider@1234
09-02-2019, 05:14 AM
Mana Sir sorry to disturb u but it was not BasketOrder..csv it is BasketOrder..xlsx, in this post we dont have any csv file it is all xls file or xlsx file(1.xls is right and BasketOrder..xlsx is right), BasketOrder..csv is not there
so plz relook sir in future this mistake will not be repeated