Excel Macro宏 - 指定格式的单元格（第二行以下若干不连续列，有数据最后一行为止）删除

Attribute VB_Name = "ClearData"

'2015/06/25 ADD By MAOUIZAYOI

Option Explicit

' Excute Interface.
Sub ClearData()

    ' define variables
    Dim ccnSht As Object
    Dim clr1Rge As Range
    Dim clr2Rge As Range
    Dim targetRge As Range
    Dim tempCell As Range
    Dim rowNo As Long
    
    ' UsedField
    Set ccnSht = ActiveSheet.UsedRange
    
    ' The max row NO that data exits.
    rowNo = ccnSht.Rows.Count

    ' ColumnA-ColumD && Row2-RowMax
    Set clr1Rge = Range("A2", "D" & rowNo)
    
    ' ColumF && Row2-RowMax
    Set clr2Rge = Range("F2", "F" & rowNo)
    
    ' The union of clr1Rge1 and clr2Rge
    Set targetRge = Union(clr2Rge, clr1Rge)

    ' Loop it and clear content of every cell.
    For Each tempCell In targetRge
        If tempCell.Value <> "" Then
            tempCell.ClearContents
        End If
    Next tempCell

End Sub

PS：这里提醒说明

1，Union(clr2Rge, clr1Rge) 1，2的顺序（原因不解释，否则删除效率慢自己可以试下）

2，Set clr1Rge = Range("A2", "D" & rowNo)这些赋值时候，不要用

ccnSht.Range("A2", "D" & rowNo)来赋值，负责会下窜一行。因为是以对应母体相对的位置。