Delete contents across multiple files

cotech_10

Board Regular
Joined
Sep 11, 2010
Messages
112
Office Version
  1. 2016
Platform
  1. Windows
Hi All,


I would like to create a vb script to allow me to clear contents in any number of excel files starting from the cell location A2.




1. The files are excel csv files.

2. Start CELL location is always A2 in each csv file

3. These files will be located in a default folder location:

eg: c:\mydata

4. All files located in that folder would need to be processed


I hope to hear from someone soon.


Thank You All
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Saurabhj

Active Member
Joined
Jun 6, 2020
Messages
413
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Hi,

Two queries:

Do the files have single sheet or multiple sheets?

If single sheet, is the sheet name same in all files?

Thanks,
Saurabh
 

Saurabhj

Active Member
Joined
Jun 6, 2020
Messages
413
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Hi,

Assuming all files have single sheet. Use below code.

VBA Code:
Sub clearContentCell()
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    Dim wb As Workbook
    Dim file As String
    Dim filepath As String, totalRows As Integer
    Dim fileCount As Integer, fileNum As Integer
    
    filepath = "C:\Home Data\Saurabh\2020\Digital marketing\YouTube\Excel\Help-Queries\FileCount\"
    
    file = Dir(filepath)

    While (file <> "")
        Workbooks.Open filepath & "\" & file
        Set wb = ActiveWorkbook
        totalRows = WorksheetFunction.CountA(wb.ActiveSheet.Range("A:A"))
        wb.ActiveSheet.Range("2:" & totalRows).ClearContents
        wb.Save
        wb.Close
        file = Dir
    Wend
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
End Sub
 

mohadin

Well-known Member
Joined
Mar 22, 2015
Messages
1,091
Office Version
  1. 2013
Platform
  1. Windows
May be
VBA Code:
Sub Test()
    Dim fPATH As String, fNAME As String
    Dim wb As Workbook, ofs As Worksheet
    fPATH = "C:\mydata\"
    fNAME = Dir(fPATH & "*.*")
    Do While Len(fNAME) > 0
        If Right(fNAME, 4) = ".csv" Then
            Set wb = Workbooks.Open(fPATH & fNAME)
           Cells(2, 1).CurrentRegion.ClearContents
            wb.Close True   'close data workboo
            fNAME = Dir
        End If
    Loop
End Sub
 

cotech_10

Board Regular
Joined
Sep 11, 2010
Messages
112
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Hi,

Assuming all files have single sheet. Use below code.

VBA Code:
Sub clearContentCell()
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
   
    Dim wb As Workbook
    Dim file As String
    Dim filepath As String, totalRows As Integer
    Dim fileCount As Integer, fileNum As Integer
   
    filepath = "C:\Home Data\Saurabh\2020\Digital marketing\YouTube\Excel\Help-Queries\FileCount\"
   
    file = Dir(filepath)

    While (file <> "")
        Workbooks.Open filepath & "\" & file
        Set wb = ActiveWorkbook
        totalRows = WorksheetFunction.CountA(wb.ActiveSheet.Range("A:A"))
        wb.ActiveSheet.Range("2:" & totalRows).ClearContents
        wb.Save
        wb.Close
        file = Dir
    Wend
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
End Sub
Hi Saurabhj,

Thank you for your response... There is only one sheet in each CSV file, and each sheet within a csv file has default standard excel naming.

I will try your VB script and will get back to you so tell you how it went.


Cheers


Cotech_10
 

cotech_10

Board Regular
Joined
Sep 11, 2010
Messages
112
Office Version
  1. 2016
Platform
  1. Windows
Hi Saurabhj,

Thank for the code it works well, I just have one other small request, would you be able to amend the code to allow only CSV files get accessed. At the moment the code will access any file in the data directory. If you could make it that only CSV files are read that would be appreciated.

Thanks and Regards

Cotech_10
 

cotech_10

Board Regular
Joined
Sep 11, 2010
Messages
112
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Hi Saurabhj,

Thank for the code it works well, I just have one other small request, would you be able to amend the code to allow only CSV files get accessed. At the moment the code will access any file in the data directory. If you could make it that only CSV files are read that would be appreciated.

Thanks and Regards

Cotech_10
Hi Saurabhj,

One other request I would like to submit, could I also request that the script could run over multiple folder destinations in which the csv files are located, i.e. all subfolders as well ?

Thanks and Regards


Cotech_10
 

cotech_10

Board Regular
Joined
Sep 11, 2010
Messages
112
Office Version
  1. 2016
Platform
  1. Windows
Hi Mohadin,

Thank you for also your reply... that works as well..

Are you able to see if the process can be applied to subfolders under c:\mydata ?

Thanks and Regards


Cotech_10
 

mohadin

Well-known Member
Joined
Mar 22, 2015
Messages
1,091
Office Version
  1. 2013
Platform
  1. Windows
Hi Mohadin,

Thank you for also your reply... that works as well..

Are you able to see if the process can be applied to subfolders under c:\mydata ?

Thanks and Regards


Cotech_10
Hi
Sorry I' so late
I presume you've sorted out
But any way
VBA Code:
Sub test2()
    Dim Fso As Object, subfld As Object, objSubFolder As Object
    Dim fPATH As String, fNAME As Variant
    Dim wb As Workbook
    fPATH = "C:\mydata\"
    Set Fso = CreateObject("Scripting.filesystemobject")
    Set subfld = Fso.GetFolder(fPATH)
    fNAME = Dir(fPATH & "*.*")
    Do While Len(fNAME) > 0
        If Right(fNAME, 4) = ".csv" Then
            Set wb = Workbooks.Open(fPATH & fNAME)
            Cells(2, 1).CurrentRegion.ClearContents
            wb.Close True
            fNAME = Dir
        End If
    Loop
    For Each subfld In subfld.subfolders
        For Each fNAME In subfld.Files
            If Right(fNAME, 4) = ".csv" Then
                Set wb = Workbooks.Open(fNAME)
                Cells(2, 1).CurrentRegion.ClearContents
                wb.Close True
            End If
        Next
    Next
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,129,467
Messages
5,636,456
Members
416,919
Latest member
twc2c

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