excel - 从范围中排除多个单元格
问题描述
我的问题是如何从范围对象中删除一个或多个单元格?我之前问过一些非常相似的问题,有些人向我指出了这个问题:Remove cell from Range (object)
接受的答案:
Function getExcluded(ByVal rngMain As Range, rngExc As Range) As Range
Dim rngTemp As Range
Dim rng As Range
Set rngTemp = rngMain
Set rngMain = Nothing
For Each rng In rngTemp
If rng.Address <> rngExc.Address Then
If rngMain Is Nothing Then
Set rngMain = rng
Else
Set rngMain = Union(rngMain, rng)
End If
End If
Next
Set getExcluded = rngMain
End Function
Sub test()
MsgBox getExcluded(Range("A1:M10000"), Range("a10")).Address
End Sub
接受的答案仅在排除范围是单个单元格时才有效 - 至少当我尝试它时对我来说是这样。我要排除的单元格通常不止一个单元格,因此我尝试调整代码:
我的尝试:
Function getExcluded(ByVal rngMain As Range, rngExcl As Range) As Range
Dim rngTemp As Range
Dim cellTemp As Range, cellExcl As Range
Set rngTemp = rngMain
Set rngMain = Nothing
For Each cellTemp In rngTemp 'go through all cells in established range
If Intersect(cellTemp, rngExcl) Is Nothing Then 'check for each cell if it intersects with the range to be excluded; no overlap -> put it into rngMain
If rngMain Is Nothing Then
Set rngMain = cellTemp
Else
rngMain = Union(rngMain, cellTemp)
End If
Debug.Print "cellTemp: " & cellTemp.Address
Debug.Print "rngMain: " & rngMain.Address
End If
Next cellTemp
Set getExcluded = rngMain
Sub test5()
getExcluded(Range("A1:D3"), Range("B1:C1")).Select
End Sub
问题似乎发生在线路上Set rngMain = Union(rngMain, rng)
。我的Debug.Print
陈述告诉我,cellTemp
它应该被迭代;然而,即使与Union
被执行的行,不管是什么cellTemp
,rngMain
保持$A$1
我究竟做错了什么?
解决方案
像这样,也设置联合范围
Function testexclude(rngMain As Excel.Range, rngExclude As Excel.Range) As Excel.Range
Dim c As Excel.Range
Dim r As Excel.Range
For Each c In rngMain
If Intersect(c, rngExclude) Is Nothing Then
If r Is Nothing Then
Set r = c
Else
Set r = Union(r, c)
End If
End If
Next c
Set testexclude = r
End Function
推荐阅读
- ssl - Tomcat 9 - 如何将 ssl 证书与 pem 和 crt 文件一起使用
- powershell - 为什么 Resolve-DnsName 在不同的计算机上表现不同?
- google-cloud-build - 云构建错误 - python3 的拉取访问被拒绝
- javascript - 图标上发生onclick功能时如何打开每张卡片的选项(下拉菜单)?
- python - 有条件的 - 尝试 x 秒
- airflow - 成功安装 python 依赖项后,MWAA 的 Web UI 上未添加连接类型
- spring - 来自 Coinbase 的 OAuth2 重定向重置并再次请求授权
- node.js - node-forge 使用certificationRequestFromPem 获取CSR 的SHA-256 Hash 和MD5 Hash
- mysql - SQL - JOIN 在另一个表中添加附加要求
- typescript - 如何将部分分配给类函数参数,部分类型来自字段?