如何编写VBA代码删除值全相同或空白的整列？

Hey there! Let's sort out this problem for you. Below is a VBA procedure that scans your worksheet and deletes any column where every cell is either the same value or completely blank—exactly what you need to remove that "Amount" column in your example.

Sub DeleteUniformOrBlankColumns()
    Dim targetSheet As Worksheet
    Dim lastUsedCol As Long
    Dim currentCol As Long
    Dim referenceValue As Variant
    Dim cell As Range
    Dim isColumnUniform As Boolean
    
    ' Set the worksheet to process (replace with your sheet name if needed)
    Set targetSheet = ThisWorkbook.ActiveSheet
    
    ' Get the last column with data in the sheet
    lastUsedCol = targetSheet.Cells(1, targetSheet.Columns.Count).End(xlToLeft).Column
    
    ' Loop from last column backwards to avoid shifting issues when deleting
    For currentCol = lastUsedCol To 1 Step -1
        isColumnUniform = True
        referenceValue = ""
        
        ' Find the first non-blank value in the column to use as a benchmark
        For Each cell In targetSheet.Columns(currentCol).Cells
            If Not IsEmpty(cell.Value) Then
                referenceValue = cell.Value
                Exit For
            End If
        Next cell
        
        ' Check all cells in the column against the reference value
        For Each cell In targetSheet.Columns(currentCol).Cells
            ' Stop checking once we pass the used range to save time
            If cell.Row > targetSheet.UsedRange.Row + targetSheet.UsedRange.Rows.Count - 1 Then
                Exit For
            End If
            
            ' If we find a non-blank cell that doesn't match the reference, mark column as non-uniform
            If Not IsEmpty(cell.Value) And cell.Value <> referenceValue Then
                isColumnUniform = False
                Exit For
            End If
        Next cell
        
        ' Delete the column if all values are uniform or blank
        If isColumnUniform Then
            targetSheet.Columns(currentCol).Delete
        End If
    Next currentCol
End Sub

Quick Breakdown of How It Works: #

• Backward Looping: We start from the rightmost column and move left. This avoids column shifting messing up our loop logic (deleting a column while looping forward would throw off subsequent column numbers).
• Reference Value Check: The code first grabs the first non-blank value in the column as a benchmark. If the column is entirely blank, this stays as an empty string.
• Uniformity Validation: It checks every cell in the column (only within the used range to save processing time). If any non-blank cell doesn't match the reference value, the column is flagged as non-uniform and skipped.
• Column Deletion: If all cells are either blank or match the reference value, the column gets deleted immediately.

Quick Usage Tips: #

• Always save your workbook before running VBA code—better safe than sorry!
• If you want to target a specific sheet instead of the active one, replace ThisWorkbook.ActiveSheet with ThisWorkbook.Worksheets("YourSheetName") (swap in your actual sheet name).

内容的提问来源于stack exchange，提问作者Ver