excel - 如何使用单元格地址作为 Range() 的参数?
问题描述
我有一个模板文件,我将用它来填充更多文件,我需要根据它选择的内容隐藏一些行,但同时我不能隐藏其他行。如果数据始终保持相同大小,我可以做得很好,但文件会根据信息增加和减少。
我在 C 列中有一系列值。我试图做的是查找包含“Pack”的单元格值(所有文件都相同)。从包含“Pack”的那个单元格(假设现在在 C8,但可以在其他文件的 C30 中)我需要开始寻找不等于我从下拉列表(划船)中获得的值和隐藏行。
也许更好的解释,我也尝试做的是分配一个变量来保存下拉列表的值,然后查找不相等的值并简单地隐藏它。然后执行 .Find() 来查找“Pack”字样。找到后,获取单元格地址。最后取该地址并将其用作 Range() 中的参数,正如您在我编写的代码中看到的那样: For Each cell In Range("packR:C5") 我知道这是非常错误的,因为我无法通过那。
Dim cell As Range
Dim pack As Range
rowing = Range("A2").Value
Set pack = Range("C1:C12").Find("Pack")
Set packA = Range(pack.Address)
Set packR = packA
For Each cell In Range("packR:-end point here")
cell.EntireRow.Hidden = False
If Not IsEmpty(cell) Then
If cell.Value <> rowing Then
cell.EntireRow.Hidden = True
End If
End If
Next
我的vba背景很少,但通过研究我可以理解一些。基本上,目标是忽略“Pack”顶部的所有行,并开始从“Pack”(需要有一个单元格地址)到 excel 文件的末尾查找。最大的问题是获取该单元格地址并将其用作 Range ("":"") 的参数。
解决方案
我想你正在寻找这样的东西。请注意有关指定其他参数的注释Range.Find
。
Sub Test()
Dim ws As Worksheet
Set ws = ActiveSheet
Dim rowing As Variant
rowing = ws.Range("A2").Value
Dim pack As Range
Set pack = ws.Range("C1:C12").Find("Pack") '<--- you should specify the other parameters of Find
Dim lastCell As Range
Set lastCell = ws.Cells(ws.Rows.Count, "C").End(xlUp)
If Not pack Is Nothing Then '<--- tests to see if pack was found
Dim cell As Range
For Each cell In ws.Range(pack, lastCell)
If Not IsEmpty(cell) Then
cell.EntireRow.Hidden = (cell.Value <> rowing)
End If
Next
End If
End Sub
编辑:
End(xlUp)
如果行已经隐藏,将找不到真正的最后一行。为了解决这个问题,这里有两种选择:
- 找到“Pack”后取消隐藏所有行。
Sub Test()
Dim ws As Worksheet
Set ws = ActiveSheet
Dim rowing As Variant
rowing = ws.Range("A2").Value
Dim pack As Range
Set pack = ws.Range("C1:C12").Find("Pack") '<--- you should specify the other parameters of Find
If Not pack Is Nothing Then '<--- tests to see if pack was found
ws.UsedRange.EntireRow.Hidden = False '<--- unhide all rows so as to find the last cell properly
Dim lastCell As Range
Set lastCell = ws.Cells(ws.Rows.Count, "C").End(xlUp)
Dim cell As Range
For Each cell In ws.Range(pack, lastCell)
If Not IsEmpty(cell) Then
cell.EntireRow.Hidden = (cell.Value <> rowing)
End If
Next
End If
End Sub
- 使用另一种方法来查找最后一个单元格:
Sub Test()
Dim ws As Worksheet
Set ws = ActiveSheet
Dim rowing As Variant
rowing = ws.Range("A2").Value
Dim pack As Range
Set pack = ws.Range("C1:C12").Find("Pack") '<--- you should specify the other parameters of Find
Dim lastCell As Range
Set lastCell = GetLastCell(ws, 3)
If Not pack Is Nothing Then '<--- tests to see if pack was found
Dim cell As Range
For Each cell In ws.Range(pack, lastCell)
If Not IsEmpty(cell) Then
cell.EntireRow.Hidden = (cell.Value <> rowing)
End If
Next
End If
End Sub
Private Function GetLastCell(ByVal ws As Worksheet, Optional ByVal colNum As Long = 1) As Range
With ws
Dim lastCell As Range
Set lastCell = .Columns(colNum).Find(What:="*", _
After:=.Cells(1, colNum), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False)
If lastCell Is Nothing Then
Set lastCell = .Cells(1, colNum)
End If
End With
Set GetLastCell = lastCell
End Function
推荐阅读
- aws-sdk - AWS 节点 JS MFA Cognito
- c - 在封装的内联函数中返回
- batch-file - 如何给命令行输入?
- javascript - 如何使用 rel="preload" as="style" 或 as="script" 或更好的页面速度方法
- apache-kafka - 为什么我在检索存储以查询它时偶尔会收到 InvalidStateStoreException PARTITIONS_REVOKED,而不是 RUNNING?
- vue.js - Vue.js - 方法与计算。这里有什么问题
- google-apps-script - 文件夹的 Google Drive API 自定义角色
- javascript - 反应 this.props.title 为空!!为什么?
- android - android.view.InflateException: Binary XML file line #18: Binary XML file line #18: Error inflating class Button
- typescript - 什么是快速而肮脏的扩展/分配?