Option Explicit
Sub 選取重複或空白列()
Dim Brr, Z, i&, j%, T$, T1$, xU As Range, C%
'↑宣告變數
Set Z = CreateObject("Scripting.Dictionary")
'↑令Z變數是 字典
Brr = Range([CQ1], Cells(ActiveSheet.UsedRange.Rows.Count, "A"))
'↑令Brr變數是帶入指定範圍儲存格值的二維陣列
C = UBound(Brr, 2): T1 = Application.Rept("/", C)
'↑C變數是Brr陣列橫向最大索引欄號,令T1變數是C變數個連續"/"符號的字串
For i = 2 To UBound(Brr)
'↑設順迴圈!i從2到 Brr陣列縱向最大索引列號
For j = 1 To C: T = T & "/" & Brr(i, j): Next
'↑設順迴圈!j從1到 C變數,令T變數是以"/"符號連接各迴圈陣列值的新字串
If Z(T) <> "" Or T = T1 Then
'↑如果以T變數查Z字典回傳item不是空的 或T變數同 T1變數?
If xU Is Nothing Then
'↑如果xU變數是空的?
Set xU = Cells(i, 1)
'↑令xU變數是 i迴圈列A欄儲存格
Else
Set xU = Union(xU, Cells(i, 1))
'↑否則就令 i迴圈列A欄儲存格納入 xU變數中
End If
End If
Z(T) = 1: T = ""
'↑令Z字典中 key為T變數的 item值是1,令T變數清空
Next
If xU Is Nothing Then MsgBox "沒有重複列": Exit Sub
'↑如果xU變數還是空的!就跳出提視窗~~~,之後結束程式執行
xU.EntireRow.Select
'↑如果xU變數不是空的!就將其所在的列選取
End Sub作者: singo1232001 時間: 2023-12-13 16:42
Sub t3()
i = Split("Provider=Microsoft.,Jet.OLEDB.4,.0;Extended Properties=Excel ,8,.0;Data Source=", ",")
If Application.Version > 12 Then i(1) = "ACE.OLEDB.12": i(3) = 12
Set cn = CreateObject("adodb.connection"): cn.Open Join(i, "") & ThisWorkbook.FullName
For i = 1 To 47
x = x & "& iif(IsNull([" & i & "]),"""",""." & i & """)"
Next
q = "select mid(b,2,999) from(" & "select " & Mid(x, 2, 99999) & " as b from "
[cv:cx].ClearContents: [CV2].CopyFromRecordset cn.Execute(q & "[sheet1$a1:au])")
[CW2].CopyFromRecordset cn.Execute(q & "[sheet1$aw1:cq])")
[CX2].CopyFromRecordset cn.Execute("select F1 &""&"" &F2 from [sheet1$CV1:CW]")
End Sub
