CODEWITHSUNDEEP
vbaexcel
Tom Chambers
Tom Chambers

Reputation: 374

Deleting columns in excel using visual basic?

I have about 750 excel files. I need to clean them so that they all contain same formatting - i.e. so they all contain same number of columns.

Some files (80%) contain extra columns containing labels with an asterisk e.g. "*1 subject".

Is there a way using visual basic to go through all of the files in my folder to delete all columns containing an asterisk so that all files don't have any such columns? Will the fact that an asterisk is a wild card in computer speak make a difference?

Upvotes: 1

Views: 2092

Answers (1)

MatthewD
MatthewD

Reputation: 6801

Write a macro that uses filesystemobjects to loop through directory where the spreadsheets are. Loop through each sheet and analyse the column names.

Here is how you would loop through each sheet.

Private Sub CommandButton7_Click()

Dim ws      As Excel.Worksheet
Dim iCol    As Integer
Dim strName As String
Dim iIndex  As Integer

    'Loop through the sheets.
    For iIndex = 1 To Application.Worksheets.Count
        Set ws = Application.Worksheets(iIndex)

        'Loop through the columns.
        For iCol = 1 To ws.UsedRange.Columns.Count
            'Check row 1 of this column for first char of *
            If Left(ws.Cells(1, iCol).Value, 1) = "*" Then
                'We have found a column with the first char of *
                ws.Columns(iCol).EntireColumn.Delete
            End If
        Next iCol

    Next iIndex
    ActiveWorkbook.SaveAs Filename:="C:\temp\newfiles\" & ActiveWorkbook.Name, FileFormat:=xlWorkbookNormal
End Sub

If you want to look for an * anywhere in the cell you could use instr()

Private Sub CommandButton7_Click()

Dim ws      As Excel.Worksheet
Dim iCol    As Integer
Dim strName As String
Dim iIndex  As Integer

    'Loop through the sheets.
    For iIndex = 1 To Application.Worksheets.Count
        Set ws = Application.Worksheets(iIndex)

        'Loop through the columns.
        For iCol = 1 To ws.UsedRange.Columns.Count
            'Check row 1 of this column for the char of *
            If instr(ws.Cells(1, iCol).Value, "*") > 0 Then
                'We have found a column with the char of *
                ws.Columns(iCol).EntireColumn.Delete
            End If
        Next iCol

    Next iIndex
    ActiveWorkbook.SaveAs Filename:="C:\temp\newfiles\" & ActiveWorkbook.Name, FileFormat:=xlWorkbookNormal
End Sub

Here is a basic loop files in a given directory. Hope this gets you there.

Private Sub CommandButton7_Click()
    Dim wb      As Workbook
    Dim ws      As Excel.Worksheet
    Dim iCol    As Integer
    Dim strName As String
    Dim iIndex  As Integer
    Dim strPath As String
    Dim strFile As String

    strPath = "c:\temp\oldfiles\"
    strFile = Dir(strPath & "*.xlsx")

    Do While strFile <> ""

        Set wb = Workbooks.Open(Filename:=strPath & strFile)

        'Loop through the sheets.
        For iIndex = 1 To Application.Worksheets.Count
            Set ws = Application.Worksheets(iIndex)

            'Loop through the columns.
            For iCol = 1 To ws.UsedRange.Columns.Count
                'Check row 1 of this column for the char of *
                If InStr(ws.Cells(1, iCol).Value, "*") > 0 Then
                    'We have found a column with the char of *
                    ws.Columns(iCol).EntireColumn.Delete
                End If
            Next iCol

        Next iIndex
        wb.SaveAs Filename:="C:\temp\newfiles\" & wb.Name, FileFormat:=xlOpenXMLWorkbook
        wb.Close SaveChanges:=False
        strFile = Dir
    Loop

End Sub

Upvotes: 3

Related Questions