Looping through files in folder & clearing data

korgro

New Member
Joined
Jan 11, 2014
Messages
6
Hi guys,

I've got a folder with 17,000+ csv files. I'm trying to write some kind of script that will loop through all the files and delete unwanted data to reduce file size.

The range I need to delete varies. So it needs to look in column J and go down until it reach the row where the cell becomes empty, then clear everything (all rows and columns) below that.

Any ideas on how I can do that?

After a bit of Googling I found some tutorials on Dir loops. But they were all for compositing file names, not quite what I need. I learnt a bit of VBA years ago but have forgotten everything now :(

Appreciate the help!
 

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

VBA Geek

MrExcel MVP
Joined
Dec 16, 2013
Messages
2,857
Down column J you got the name of the files you wish to delete?
 

korgro

New Member
Joined
Jan 11, 2014
Messages
6
I've found a way to delete what I want. But I still don't know how to repeat this for all the csv files in the folder.

Sub Test()

Dim rFind As Range

With Range("h200:h5000")
Set rFind = .Find("demandforecast")
If Not rFind Is Nothing Then

With ActiveSheet
.Rows(rFind.Row & ":" & .Rows.Count).Delete
End With

End If
End With

End Sub
 

korgro

New Member
Joined
Jan 11, 2014
Messages
6
Finally got a code working. But it's pretty slow. Not sure if it's because there are so many files or if the code could be more efficient. Any ways of making the opening, saving and closing process faster?

Code:
Sub DirLoop()
 
      Dim MyFile As String, MyPath As String, MyWorkbook As Workbook, rFind As Range
 
      MyPath = "c:\users\desktop\test\"
     
      MyFile = Dir(MyPath & "*.csv")
 
      Do While MyFile <> ""
     
        Set MyWorkbook = Workbooks.Open(MyPath & MyFile)
 
        With ActiveSheet.Range("h1:h5000")
            Set rFind = .Find("demandforecast")
            If Not rFind Is Nothing Then
                
                With ActiveSheet
                    .Rows(rFind.Row & ":" & .Rows.Count).Delete
                End With
               
            End If
        End With
       
        MyWorkbook.Close True
 
        MyFile = Dir()
      Loop
 
   End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,127,334
Messages
5,624,089
Members
416,010
Latest member
NJT

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top