[Excel VBA] How to Automatically Delete Columns Containing Only Dashes (‘-‘)

When organizing data in Excel, you may encounter columns that are completely filled with dashes (‘-‘). These columns are often unnecessary and clutter your spreadsheet.

In this article, I will explain how to use VBA to automatically delete these specific columns.

Prerequisites

To use this code, the following conditions apply:

1. Target Range: The columns to be checked are within a specific range (e.g., Columns 5 to 11).
2. Row Range: The rows to be checked are also fixed (e.g., Rows 7 to 13).
3. Deletion Condition: A column will only be deleted if every single cell in the specified row range contains a dash (‘-‘).
4. Environment: Macros must be enabled in Excel.

The VBA Code

Below is the VBA code to remove columns that consist entirely of dashes.

Sub DeleteDashColumns()
    Dim outputSheet As Worksheet
    Dim startRow As Long
    Dim lastRow As Long
    Dim startCol As Long
    Dim lastCol As Long
    Dim col As Long
    Dim row As Long
    Dim deleteFlag As Boolean

    ' Set the target worksheet
    Set outputSheet = ThisWorkbook.Sheets("TargetSheetName") ' Change sheet name as needed

    ' Set the row range
    startRow = 7 ' Start Row
    lastRow = 13 ' End Row

    ' Set the column range
    startCol = 5 ' Start Column
    lastCol = 11 ' End Column

    ' Check and delete columns in reverse order
    For col = lastCol To startCol Step -1
        deleteFlag = True ' Initialize flag

        ' Check all rows within the specified range
        For row = startRow To lastRow
            If outputSheet.Cells(row, col).Value <> "-" Then
                deleteFlag = False ' If a value other than '-' is found, do not delete
                Exit For
            End If
        Next row

        ' If the flag is still True, delete the column
        If deleteFlag Then
            outputSheet.Columns(col).Delete
        End If
    Next col
End Sub

Explanation of the Code

1. Setting the Target Sheet

Set outputSheet = ThisWorkbook.Sheets("TargetSheetName")

Specify the worksheet containing the table you want to clean. Please change "TargetSheetName" to your actual sheet name.

2. Defining the Range

startRow = 7
lastRow = 13
startCol = 5
lastCol = 11

• startRow and lastRow define which rows to check. In this example, it checks rows 7 through 13.
• startCol and lastCol define which columns to check. In this example, it checks columns 5 through 11.

3. Processing Columns in Reverse

For col = lastCol To startCol Step -1

We process the columns from right to left (reverse order). This is crucial because when you delete a column, the remaining columns shift to the left. If you loop normally (left to right), this shifting causes the code to skip columns or check the wrong data. Looping backwards prevents this error.

4. Checking the Cells

For row = startRow To lastRow
    If outputSheet.Cells(row, col).Value <> "-" Then
        deleteFlag = False
        Exit For
    End If
Next row

The code checks every cell in the specified row range for the current column. If it finds even one cell that is not a dash (-), it sets deleteFlag to False and stops checking that column.

5. Deleting the Column

If deleteFlag Then
    outputSheet.Columns(col).Delete
End If

If the loop finishes and deleteFlag is still True (meaning all cells were dashes), the entire column is deleted.

Summary

Using this VBA code, you can easily remove unnecessary columns filled with dashes. This is very useful for efficient data cleaning and organizing imported data.