问题描述
基于存储在特定单元格(例如工作表的A1
)中的值,我想在工作表中隐藏从B列开始的许多列.
Based on the value stored in a specific cell such as A1
of a worksheet I would like to hide a number of columns in the worksheet starting with column B.
我想做的事的例子:
- 如果单元格A1的值= 10,则隐藏B列以及B后面的10列
- 如果单元格A2的值= 11,则隐藏B列以及B后面的11列
困难实际上是Excel(或至少我的Excel文件)使用字母(A,B,...)作为列名称的方式.在使用rows("2:" & range("A1").value)
这样的代码并设置.hide = true
The difficulty is actually the way Excel (or least my Excel files) uses the alphabet (A, B, ...) for the name of the columns. I have done this on rows before using code like rows("2:" & range("A1").value)
and set .hide = true
推荐答案
您可以按索引编号引用列,例如: Columns(indexnumber)
,您可以使用 Resize()
设置要选择的列数,如下所示:
You can reference columns by their index number as such: Columns(indexnumber)
and you can use Resize()
to set the number of columns you want to select like so:
Sub HideColumns()
Dim numColumnsToHide
numColumnsToHide = Cells(1, 1).Value
Columns(2).Resize(, numColumnsToHide).Select
Selection.EntireColumn.Hidden = True
End Sub
很显然,此代码没有对A1
中的值进行任何验证,因此,如果有人在A1
中没有整数的情况下运行HideColumns()
,则可能会发生不良情况.这也不会取消隐藏任何隐藏的列.
Obviously, this code doesn't have any validation of value in A1
so if someone runs HideColumns()
without an integer in A1
, bad things are going to happen. This also doesn't unhide any hidden columns.
这篇关于在Excel 2010中使用VBA隐藏列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!