excel - 使用可能的 With 语句将范围内的可见单元格转换为合并单元格。Excel VBA
问题描述
有没有办法可以用以下内容制作 with 声明?
If ActiveSheet.Range("B17").EntireRow.Hidden = False Then
If Range("C17").Value = "" Then
With Range("C17:E17")
.Locked = False
.Merge
.HorizontalAlignment = xlCenterAcrossSelection
.VerticalAlignment = xlCenter
End With
With Range("F17:G17")
.Locked = False
.Merge
.HorizontalAlignment = xlCenterAcrossSelection
.VerticalAlignment = xlCenter
End With
With Range("H17:J17")
.Locked = False
.Merge
.HorizontalAlignment = xlCenterAcrossSelection
.VerticalAlignment = xlCenter
End With
End If
End If
If ActiveSheet.Range("B18").EntireRow.Hidden = False Then
If Range("C18").Value = "" Then
With Range("C18:E18")
.Locked = False
.Merge
.HorizontalAlignment = xlCenterAcrossSelection
.VerticalAlignment = xlCenter
End With
With Range("F18:G18")
.Locked = False
.Merge
.HorizontalAlignment = xlCenterAcrossSelection
.VerticalAlignment = xlCenter
End With
With Range("H18:J18")
.Locked = False
.Merge
.HorizontalAlignment = xlCenterAcrossSelection
.VerticalAlignment = xlCenter
End With
End If
End If
工作表上的范围从 B17 - B31 开始,上面的代码对于整个范围都是相同的。
感谢您的时间。
解决方案
你的意思是这样的吗?
dim i as int
for i= 17 to 31
If ActiveSheet.Range("B" & i).EntireRow.Hidden = False Then
If Range("C" & i).Value = "" Then
With Range("C" & i & ":E" & i)
.Locked = False
.Merge
.HorizontalAlignment = xlCenterAcrossSelection
.VerticalAlignment = xlCenter
End With
With Range("F" & i & ":G" & i)
.Locked = False
.Merge
.HorizontalAlignment = xlCenterAcrossSelection
.VerticalAlignment = xlCenter
End With
With Range("H" & i & ":J" & i)
.Locked = False
.Merge
.HorizontalAlignment = xlCenterAcrossSelection
.VerticalAlignment = xlCenter
End With
End If
End If
next i
(您可以为列合并执行另一个嵌套的 for 循环以使其更高效,但只有 3 个块,将它们保留为您拥有它们并不会太烦人)
推荐阅读
- javascript - 如何将令牌从服务器发送到客户端?
- scala - 从 Spark 服务器执行 SFTP 时,大型机服务器上的记录级别数据截断
- javascript - 无法解构属性,因为它是未定义的
- node.js - 我的 Heroku 页面上的应用程序错误,即使我的应用程序构建成功
- javascript - 未找到 webpack 模块功能
- javascript - Firebase 管理员无法确定项目 ID
- kubernetes - 更改 Kubernetes 上工作机器的公开版本
- laravel - 身份验证不断要求输入密码
- python - postgres/sqlalchemy - 如何向表的现有列添加唯一约束
- scikit-learn - 决策树节点值