Andy2483

回復 1# sschristy


    http://forum.twbts.com/viewthrea ... mp;page=1#pid109593

sschristy

回復 11# Andy2483


    感謝大大的幫忙，馬上來研究

sschristy

回復 10# Andy2483


    感謝ANDY大大，那請問清除項目是否也可以做到多列一次清除？

下載 (10.78 KB)

2023-10-23 16:54

以下是目前的程式碼，只能一次刪一列
Private Sub CommandButton2_Click()
Dim b As Integer
b = (ActiveCell.Row)
Range("A" & b & ":G" & b).ClearContents
End Sub

Andy2483

回復 13# sschristy


Private Sub CommandButton2_Click()
Dim xA As Range, xB As Range, xI As Range
Set xA = Range([A1], ActiveSheet.UsedRange)
Set xB = [A:G]
Set xI = Intersect(xA, xB)
xI.Offset(3, 0).ClearContents
End Sub

https://learn.microsoft.com/zh-t ... worksheet.usedrange
https://learn.microsoft.com/zh-t ... plication.intersect
https://learn.microsoft.com/zh-t ... /excel.range.offset

Andy2483

回復 13# sschristy


    以下是複習的心得註解,請前輩參考

Private Sub CommandButton2_Click()
Dim xA As Range, xB As Range, xI As Range
'↑宣告(xA,xB,xI)各是儲存格變數
Set xA = Range([A1], ActiveSheet.UsedRange)
'↑令xA這儲存格變數是涵蓋[A1]儲存格與此表已使用儲存格,
'涵蓋這區間的最小方正範圍儲存格
Set xB = [A:G]
'↑令xB這儲存格變數是[A:G]儲存格
Set xI = Intersect(xA, xB)
'↑令xI這儲存格變數是 xA與xB變數交集的儲存格
xI.Offset(3, 0).ClearContents
'↑令xI變數往下偏移3列的這新範圍儲存格清除其內容
End Sub

sschristy

回復 15# Andy2483


    感謝ANDY大大，請問有辦法只批次刪除所選的欄位嗎？

Andy2483

回復 16# sschristy


    以下是批次刪除選取的列資料(選取格列攔>按鈕) 或 全部列資料刪除(整表選取>按鈕)

Private Sub CommandButton2_Click()
Dim Q, A$, i&, n&, xI As Range, Brr, xA As Range, xB As Range
Set xA = Selection.Cells: Set xB = ActiveSheet.UsedRange
If xA.EntireRow.Count > xB.Rows.Count Then Set xA = Intersect(xA, xB)
If xA Is Nothing Then Exit Sub
Set xI = Intersect(Range([A1], xB), [A:G]): Brr = xI
For Each Q In Split(xA.EntireRow.Address(0, 0), ",")
   For i = Split(Q, ":")(0) To Split(Q, ":")(1)
      A = IIf(A = "", "/" & Val(i) & "/", A & Val(i) & "/")
   Next
Next
For i = 4 To xI.Rows.Count
   If InStr(A, "/" & i & "/") Then GoTo i01 Else n = n + 1
   For j = 1 To UBound(Brr, 2): Brr(n, j) = Brr(i, j): Next
i01: Next
xI.Offset(3, 0).ClearContents
If n = 0 Then Exit Sub
[A4].Resize(n, UBound(Brr, 2)) = Brr
End Sub

Andy2483

回復 16# sschristy


    以下另一方法,請前輩參考
Private Sub CommandButton2_Click()
Dim xU As Range, xA As Range, xB As Range
Set xA = Selection.Cells: Set xB = ActiveSheet.UsedRange.Offset(3)
Set xU = Union(xA, xB)
If xU.Count <> xB.Count Then Exit Sub
Intersect(xA.EntireRow, [A:G]).Delete Shift:=xlUp
End Sub

rtyfghvbn520

我是積分不足無法發文
努力中

sschristy

回復 10# Andy2483

ANDY大大，請問此程式碼能不能增加一項功能？
就是假設我選了四行但加入到主頁時會有五行，而第五行為空白列。
這樣的用意在比較好分辨每一次加入的內容。
謝謝