由于将 PDF 转换为 Excel，我有许多杂乱数据的工作表。
我选择工作表的一部分并将其定义为名为“GP_Data”的表。
我正在尝试删除工作表中除我定义的表之外的所有其他内容。
有没有办法做到这一点？
我已经根据我在此处看到的另一个答案尝试了以下内容...但似乎类型不匹配，因为 GP_Data 是我的表名，我认为 VBA 似乎不喜欢将表定义为范围。
Dim xRg As Range
Dim xCell As Range
Dim xAddress As String
xAddress = Application.ActiveWindow.RangeSelection.Address
Set xRg = GP_Data
For Each xCell In ActiveSheet.UsedRange
If Intersect(xCell, xRg) Is Nothing Then
xCell.Clear
End If
Next
——
谢谢！
Option Explicit
Sub deleteExceptTable()
Dim urg As Range: Set urg = ActiveSheet.UsedRange
Dim trg As Range: Set trg = ActiveSheet.ListObjects("GP_Data").Range
Dim drg As Range
Dim lSize As Long
Dim cCount As Long
' Left
lSize = trg.Column - urg.Column
If lSize > 0 Then
Set drg = urg.Columns(1).Resize(, lSize)
End If
' Right
cCount = urg.Column + urg.Columns.Count - trg.Column - trg.Columns.Count
If cCount > 0 Then
Set drg = CombinedRange(drg, _
urg.Columns(lSize + trg.Columns.Count + 1).Resize(, cCount))
End If
Dim rCount As Long
' Top
rCount = trg.Row - urg.Row
If rCount > 0 Then
Set drg = CombinedRange(drg, _
Cells(urg.Row, trg.Column).Resize(rCount, trg.Columns.Count))
End If
' Bottom
rCount = urg.Row + urg.Rows.Count - trg.Row - trg.Rows.Count
If rCount > 0 Then
Set drg = CombinedRange(drg, Cells(trg.Row + trg.Rows.Count, _
trg.Column).Resize(rCount, trg.Columns.Count))
End If
If Not drg Is Nothing Then
drg.Clear
End If
End Sub
Function CombinedRange( _
ByVal BuiltRange As Range, _
ByVal AddRange As Range) _
As Range
If BuiltRange Is Nothing Then
Set CombinedRange = AddRange
Else
Set CombinedRange = Union(BuiltRange, AddRange)
End If
End Function
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句