我見過這個非常相似的問題，我認為用戶@SQL Police 的回答很棒！但我不知道如何修改或調整它以產生我正在尋找的結果。這是我的（類似但不同的）問題：
我想洗掉不在第 24 - 26 列中的所有內容，但也不在第 1 - 6 行中。
我有代碼可以單獨執行，但不能一起執行。
洗掉指定列：
Sub ColClear()
Dim ws As Worksheet
Set ws = ActiveSheet
ws.Range(ws.Columns(1), ws.Columns(23)).ClearContents
ws.Range(ws.Columns(27), ws.Columns(ws.UsedRange.End(xlToRight).Column)).Clear
End Sub
要洗掉指定的行：
Sub RowClear()
With Sheets("SheetName")
.Rows(7 & ":" & .Rows.Count).ClearContents
End With
End Sub
但是我怎么能同時做這兩個呢？（兩個范圍的交集，而不是并集）
謝謝！
uj5u.com熱心網友回復：
適應您的要求，基本思想是分別獲取雙方的范圍ClearContents：
Option Explicit
Private Sub Test()
Dim ws As Worksheet
Set ws = ActiveSheet
With ws
Dim bottomLeftRng As Range
Set bottomLeftRng = .Range(.Cells(7, 1), .Cells(.Rows.Count, 23))
Dim bottomRightRng As Range
Set bottomRightRng = .Range(.Cells(7, 27), .Cells(.Rows.Count, .Columns.Count))
End With
bottomLeftRng.ClearContents
bottomRightRng.ClearContents
End Sub
uj5u.com熱心網友回復：
遺憾的是Excel VBA物件模型沒有提供Difference()函式來補充includedUnion()和Intersect()functions。
如果它做到了，您的任務將像這樣簡單：
With Difference(ws.[x:z], ws.[1:6])
.ClearContents
End With
因此，讓我們創建一個Difference()可以使用上述代碼段呼叫的函式：
Function Difference(r1 As Range, r2 As Range) As Range
Dim x1&, x2&, x3&, x4&
Dim y1&, y2&, y3&, y4&
Dim a&, rBox As Range, rUni As Range, rInt As Range
Set rInt = Intersect(r1, r2)
Set rUni = Union(r1, r2)
Set rBox = rUni.Areas(1)
For a = 2 To rUni.Areas.Count
Set rBox = Range(rBox, rUni.Areas(a))
Next
x1 = rBox.Column
x2 = rInt.Column - 1
x3 = rInt.Column rInt.Columns.Count
x4 = rBox.Column rBox.Columns.Count - 1
y1 = rBox.Row
y2 = rInt.Row - 1
y3 = rInt.Row rInt.Rows.Count
y4 = rBox.Row rBox.Rows.Count - 1
Set rUni = Range(Cells(y3, x3), Cells(y4, x4))
If y2 Then Set rUni = Union(rUni, Range(Cells(y1, x3), Cells(y2, x4)))
If x2 Then Set rUni = Union(rUni, Range(Cells(y3, x1), Cells(y4, x2)))
If y2 > 0 And x2 > 0 Then Set rUni = Union(rUni, Range(Cells(y1, x1), Cells(y2, x2)))
Set Difference = rUni
End Function
我相信這解決了保留兩個范圍的交集和并集內容同時清除其他所有內容的一般情況。
它也適用于較小的范圍，僅清除框內緊緊圍繞聯合的角落區域：
With Difference(ws.[a9:ba11], ws.[v3:z25])
.ClearContents
End With
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/492091.html
下一篇：修改合并和匯總重復行的子