VBA Code for Clear Content from different workbook

[rush2rajen]

Hi Excel Gurus,

I am new to VBA. Started learning a while ago. I am having a folder which consists of 50 workbook with different name. There are only six columns in it but row count differs.

I only want to clear contents of the "D" Columns of all the workbooks and of only the sheet named as "DATA" without disturbing any other data.

It is very painful to delete columns from 50 workbook. In near future the workbook may increase to 100 and it will not be possible for me to clear the content individually as it will take hell lot of time.
Is there any VBA Code which will help me get the desired result ?


Thanks Rj

 

[shrivallabha]

You can try below code and see if it works for you. Since this will delete information, make sure you have backup.

Public Sub ClearDColumn()
Dim strFolder As String
Dim objFSO As Object, objFil As Object
Dim wbkProcess As Workbook
Dim wksProcess As Worksheet
'\\ Get Folder Path
With Application.FileDialog(msoFileDialogFolderPicker)
    .AllowMultiSelect = False
    .Title = "Select Folder"
    .Show
    If .SelectedItems.Count <> 0 Then
        strFolder = .SelectedItems(1)
    Else
        MsgBox "No folder selected. Exiting!", vbExclamation
        Exit Sub
    End If
End With

'\\ Loop through files in specified folder
Set objFSO = CreateObject("Scripting.FileSystemObject")
For Each objFil In objFSO.GetFolder(strFolder).Files
    If InStr(objFil.Name, ".xls") > 0 Then
        Set wbkProcess = Workbooks.Open(objFil.Path)
        On Error Resume Next
            Set wksProcess = wbkProcess.Sheets("DATA")
        On Error GoTo 0
        If Not wksProcess Is Nothing Then
            wksProcess.Range("D1:D" & wksProcess.Range("D" & Rows.Count).End(xlUp).Row).ClearContents
        End If
        wbkProcess.Close True
    End If
Next objFil

'\\ Inform action is finished
Set objFSO = Nothing: objFil = Nothing
MsgBox "Finished updating workbooks in the folder : " & strFolder

End Sub