在Excel 2010中使用VBA隐藏列 [英] Hide columns using VBA in Excel 2010

问题描述

基于存储在特定单元格(例如工作表的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

推荐答案

您可以按索引编号引用列，例如: 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隐藏列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！