vba Excel VBA中复制时如何判断是否有隐藏列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1088523/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to determine if there are hidden columns when copying in Excel VBA
提问by JFV
As the title explains, I have an Excel 2003 workbook and I'm copying a number of columns of one sheet to another in VBA. Unknown to me, someone has hidden a few columns on the source sheet and it has messed up how I process the cells in the destination sheet.
正如标题所解释的，我有一个 Excel 2003 工作簿，我正在 VBA 中将一张工作表的多列复制到另一张工作表中。我不知道，有人在源工作表上隐藏了几列，这弄乱了我处理目标工作表中单元格的方式。
How can I programmically determine:
我如何以编程方式确定：
- IF there are hidden columns
- WHICH columns are hidden?
- 如果有隐藏列
- 哪些列是隐藏的？
Thanks! JFV
谢谢！JFV
回答by Andrew Scagnelli
For a Range, check the Range.Hiddenproperty.
对于 a Range，检查Range.Hidden属性。
The following snippet from MSDN is a good example of how to hide/unhide a row/column:
以下来自 MSDN 的片段是如何隐藏/取消隐藏行/列的一个很好的例子：
Worksheets("Sheet1").Columns("C").Hidden = True
You can also test the value with an Ifstatement:
您还可以使用If语句测试该值：
For ColCounter = 1 To 10
If Columns("C").Hidden = True Then
Columns("C").Hidden = False
End If
Next
回答by Modan
If you only want to copy the visible files then one option that is quite nice it to select only the visible columns as a range.
如果您只想复制可见文件，那么一个非常好的选项是仅选择可见列作为范围。
This can be done by
这可以通过
Set visrng =rng.SpecialCells(xlCellTypeVisible)
It wasn't clear to me if this would be useful in your case, but I decided to post it as it could be useful to others.
我不清楚这对您的情况是否有用，但我决定发布它，因为它可能对其他人有用。
回答by mikebinz
Copying Visible Cells to another Range and then comparing the number of cells in each is probably the easiest way to determine if there are Hidden Cells in the Range
将可见单元格复制到另一个范围，然后比较每个范围内的单元格数量可能是确定范围中是否存在隐藏单元格的最简单方法
eg
例如
Selection.SpecialCells(xlCellTypeVisible).Copy Destination:=VisRan
If Not Selection.Cells.Count = VisRan.Cells.Count Then
MsgBox "Selection contains Hidden Cells"
End If
回答by gjutras
You can check by using a function like:
您可以使用以下函数进行检查：
Function IsColumnHidden(column As Variant) as Boolean
IsColumnHidden = False
If Columns(column).ColumnWidth = 0.0 Then IsColumnHidden = True
End Function
A Column width or Row height of 0.0 is an indicator of whether or not the range is hidden.
0.0 的列宽或行高指示范围是否隐藏。
回答by ADG
Here is one that I have tested and it works well if you want to hide/unhide columns
这是我测试过的一个，如果您想隐藏/取消隐藏列，它可以很好地工作
Sub Macro1_Test_Hidden() ' ' Macro1_Test_Hidden Macro ' ' ' If Columns("BH:XFA").Hidden = False Then Columns("BH:XFA").Select Range("BH:XFA").Activate Selection.EntireColumn.Hidden = True Else Columns("BH:XFA").Select Range("BH:XFA").Activate Selection.EntireColumn.Hidden = False End If ' ' End Sub
Sub Macro1_Test_Hidden() ' ' Macro1_Test_Hidden Macro ' ' ' If Columns("BH:XFA").Hidden = False Then Columns("BH:XFA").Select Range("BH:XFA").Activate Selection.EntireColumn.Hidden = True Else Columns("BH:XFA").Select Range("BH:XFA").Activate Selection.EntireColumn.Hidden = False End If ' ' End Sub
