Delete rows in each workbook based on keyword match specific column

[satish78]

Hi Friends,

I looked in to forum posts where I am looking to delete rows with keyword match in specific column(like ColumnI) in multiple workbooks in a folder.
I want use VBA to delete entire row of each workbook in folder (there are 100+ workbooks in a folder) based on keyword match(keyword is "incomplete") in ColumnI.
Vba should loop through list of workbooks in a folder.

Thanks

 

[mumps]

Does each workbook have more than one sheet in which you want to delete rows? Are you looking at column L or column i ? What is the full path to the folder where the files are saved? What is the extension of the files (xlsx, xlsm)? Are those files the only files in that folder?

 

[satish78]

Each workbook have only one sheet.
Looking at Column i.
Files exists in folder path F:\Records
We can save files in folder path F:\Uniquerecords
Extension of files .csv
they are only files in that folder.

 

[mumps]

Place this macro in a new workbook and run it from there.

Sub DeleteRows()
    Application.ScreenUpdating = False
    Dim srcWB As Workbook
    Const strPath As String = "F:\Records\"
    ChDir strPath
    strExtension = Dir(strPath & "*.csv")
    Do While strExtension <> ""
        Set srcWB = Workbooks.Open(strPath & strExtension)
        With Range("A1").CurrentRegion
            .AutoFilter 9, "incomplete"
            .AutoFilter.Range.Offset(1).EntireRow.Delete
            Range("A1").AutoFilter
        End With
        Application.DisplayAlerts = False
        srcWB.SaveAs Filename:="F:\Uniquerecords\" & srcWB.Name, FileFormat:=xlCSV, CreateBackup:=False
        Application.DisplayAlerts = True
        srcWB.Close False
    Loop
    Application.ScreenUpdating = True
End Sub

 

[satish78]

I am getting run-time error 424.
Object required at
.AutoFilter.Range.Offset(1).EntireRow.Delete

 

[mumps]

Try:

Sub DeleteRows()
    Application.ScreenUpdating = False
    Dim srcWB As Workbook
    Const strPath As String = "F:\Records\"
    ChDir strPath
    strExtension = Dir(strPath & "*.csv")
    Do While strExtension <> ""
        Set srcWB = Workbooks.Open(strPath & strExtension)
        With Range("A1")
            .CurrentRegion.AutoFilter 9, "incomplete"
            .AutoFilter.Range.Offset(1).EntireRow.Delete
            Range("A1").AutoFilter
        End With
        Application.DisplayAlerts = False
        srcWB.SaveAs Filename:="F:\Uniquerecords\" & srcWB.Name, FileFormat:=xlCSV, CreateBackup:=False
        Application.DisplayAlerts = True
        srcWB.Close False
    Loop
    Application.ScreenUpdating = True
End Sub

 

[satish78]

same error.
I just changed the path directory.

 

[mumps]

It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your csv sheet. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[satish78]

Here is the data
I want to delete rows which has Incomplete in Columni

Book1
	A	B	C	D	E	F	G	H	I
1	First_name	Last_name	Middle_name	Job_Title	Company	URL	Description	Date	Status
2	Lockwood	McKenna		Villanova University	San Francisco Bay Area	https://www.example.com	view	Wed, 20 Jan 2021 18:13:00 GMT	complete
3	Lockwood	McKenna		Villanova University	San Francisco Bay Area	https://www.example.com	view	Fri, 29 Jan 2021 23:10:00 GMT	complete
4	Lockwood	McKenna		Villanova University	San Francisco Bay Area	https://www.example.com	view	Sun, 28 Jun 2020 09:03:00 GMT	complete
5	Lockwood	McKenna		Villanova University	San Francisco Bay Area	https://www.example.com	view	Mon, 04 Nov 2019 14:13:00 GMT	complete
6	Lockwood	McKenna		Villanova University	San Francisco Bay Area	https://www.example.com	view	Wed, 08 Jul 2020 22:20:00 GMT	Incomplete
7	Lockwood	McKenna		Villanova University	San Francisco Bay Area	https://www.example.com	view	Tue, 28 Jan 2020 06:41:00 GMT	complete
8	Lockwood	McKenna		Villanova University	San Francisco Bay Area	https://www.example.com	view	Wed, 02 Dec 2020 18:14:00 GMT	complete
9	Lockwood	McKenna		Villanova University	San Francisco Bay Area	https://www.example.com	view	Fri, 22 Nov 2019 00:44:00 GMT	complete
10	Lockwood	McKenna		Villanova University	San Francisco Bay Area	https://www.example.com	view	Tue, 02 Feb 2021 11:17:00 GMT	Incomplete
11	Lockwood	McKenna		Villanova University	San Francisco Bay Area	https://www.example.com	view	Wed, 06 Nov 2019 01:03:00 GMT	complete
12	Lockwood	McKenna		Villanova University	San Francisco Bay Area	https://www.example.com	view	Tue, 05 Nov 2019 00:43:00 GMT	complete
13	Lockwood	McKenna		Villanova University	San Francisco Bay Area	https://www.example.com	view	Sat, 02 Nov 2019 15:20:00 GMT	Incomplete
14	Lockwood	McKenna		Villanova University	San Francisco Bay Area	https://www.example.com	view	Fri, 01 Nov 2019 00:40:00 GMT	complete
15	Lockwood	McKenna		Villanova University	San Francisco Bay Area	https://www.example.com	view	Thu, 12 Nov 2020 18:52:00 GMT	Incomplete
16	Lockwood	McKenna		Villanova University	San Francisco Bay Area	https://www.example.com	view	Thu, 31 Oct 2019 07:29:00 GMT	Incomplete
17	Lockwood	McKenna		Villanova University	San Francisco Bay Area	https://www.example.com	view	Mon, 11 Nov 2019 22:11:00 GMT	Incomplete
18	Lockwood	McKenna		Villanova University	San Francisco Bay Area	https://www.example.com	view	Sat, 10 Oct 2020 11:25:00 GMT	Incomplete
19	Lockwood	McKenna		Villanova University	San Francisco Bay Area	https://www.example.com	view	Sun, 23 Aug 2020 06:02:00 GMT	Incomplete
20	Lockwood	McKenna		Villanova University	San Francisco Bay Area	https://www.example.com	view	Thu, 04 Feb 2021 04:27:00 GMT	complete
Sheet1

 

[satish78]

Try:

 Sub DeleteRows() Application.ScreenUpdating = False Dim srcWB As Workbook Const strPath As String = "F:\Records\" ChDir strPath strExtension = Dir(strPath & "*.csv") Do While strExtension <> "" Set srcWB = Workbooks.Open(strPath & strExtension) With Range("A1") .CurrentRegion.AutoFilter 9, "incomplete" .AutoFilter.Range.Offset(1).EntireRow.Delete Range("A1").AutoFilter End With Application.DisplayAlerts = False srcWB.SaveAs Filename:="F:\Uniquerecords\" & srcWB.Name, FileFormat:=xlCSV, CreateBackup:=False Application.DisplayAlerts = True srcWB.Close False Loop Application.ScreenUpdating = True End Sub

I posted minisheet.
there is some error in vba code at autofilter.
Did you recheck that?.