Dim i&, xU As Range
For i = 9 To Cells(row1, Columns.Count).End(xlToLeft).Column
If Cells(row1, i) = 0 Then
If xU Is Nothing Then Set xU = Cells(row1, i) Else Set xU = Union(xU, Cells(row1, i))
End If
Next i
If Not xU Is Nothing Then xU.EntireColumn.Delete
Option Explicit
Sub TEST()
Dim Brr, Ra, Rs, Rn, j%, xU As Range, xA As Range
'↑宣告變數:(Brr,Ra,Rs,Rn)是通用型變數,j是短整數變數,(xU,xA)是儲存格變數
Set xA = Sheets("工作表1").UsedRange
'↑令xA這儲存格變數是 "工作表1" 工作表的有使用儲存格
For Each Ra In xA.SpecialCells(2)
'↑設逐項迴圈!令Ra這通用型變數是 xA儲存格集裡的非空格儲存格之一
Rn = IIf(Ra = "總計" And Rn = "" And Rs <> "", Ra.Address, Rn)
'↑令Rn這通用型變數是IIf()回傳值,
'IIf():如果Ra變數是 "總計"字串,而且Rn是空字元,並且Rs變數不是空字元?
'就令Rn變數是 Ra變數的儲存格位址,否則令Rn變數依然是 Rn變數
Rs = IIf(Ra = "總計" And Rs = "", Ra.Address, Rs)
'↑令Rs這通用型變數是IIf()回傳值,
'IIf():如果Ra變數是 "總計"字串,而且Rs是空字元,
'就令Rs變數是 Ra變數的儲存格位址,否則令Rs變數依然是 Rs變數
Next
If Rs = Empty Or Rn = Empty Then Exit Sub
'↑如果Rs變數是初始值 或 如果Rn變數是初始值!就結束程式執行
Set xA = Range(Rs, Rn)
'↑令xA這儲存格變數是 兩個(總計)儲存格擴展範圍的儲存格
Brr = xA
'↑令Brr這通用型變數是 二維陣列,以xA變數值帶入
Set xU = Cells(1, Columns.Count)
'↑令xU這儲存格變數是 第1列最後一欄儲存格
For j = 1 To UBound(Brr, 2)
'↑設順迴圈!j從1到 Brr陣列橫向最大索引欄號
If IsDate(Brr(1, j)) And Brr(UBound(Brr), j) = 0 Then
'↑如果第1列j變數欄Brr陣列值是日期,而且最後列j變數欄Brr陣列值是 0
Set xU = Union(xU, xA(j))
'↑令xA變數(儲存格)裡的 第j變數個儲存格,納入xU儲存格集裡
End If
Next
Application.Goto xU.EntireColumn
'↑令儲存格游標到 xU變數所在的欄位
Set xA = Nothing: Set xU = Nothing: Set Ra = Nothing: Erase Brr
'↑令釋放變數
End Sub作者: hcm19522 時間: 2023-3-24 11:22
