Looping through files in folder & clearing data

[korgro]

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!

 

[VBA Geek]

Down column J you got the name of the files you wish to delete?

 

[korgro]

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]

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?

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