使用VBA在Excel中隐藏表格中的空列 [英] Hiding empty columns in a table in excel using VBA

问题描述

我有一个带有表的excel表，表很大，所以我也有一个切片器将其分成可见的部分.我的问题是，然后在切片器中选择特定的单元，有时表中的列将为空.我非常想制作一个按钮来隐藏它们(然后再隐藏另一个按钮，以便我可以在切片器中选择其他单元并查看新数据，依此类推...)

I have an excel sheet with a table and its quite large so I also have a slicer to break it up in viewable pieces. My problem is that then selecting a specific unit in the slicer sometimes there will be columns in the table that's empty. I would very much like to make a button to hide them (and then have another button to unhide so I can select other units in the slicer and view new data and so on...)

我的问题是，大多数在线VBA代码都隐藏了表外的所有列.我不以任何方式流利的VBA.我尝试修改仅找到的代码未成功.根据我在网上看到的内容，大多数代码在工作表中定义一个区域/范围，然后在该范围内循环并隐藏所有空列.但是然后我尝试重新定义范围，因为ListObjects("Table1")代码失败.

My problem is that most VBA code online hides all the columns outside the table. Im not in any way fluent in VBA. I have unsuccessfully tried to modifying code I found only. From what I can see online, most codes define an area/range within a worksheet and then loops over that range and hides all empty columns. But then I try to redefine the range as the ListObjects("Table1")the code fails.

到目前为止，我设法制作了隐藏表外所有列的代码，并且我尝试制作一小段代码来隐藏

So far i have managed to make code that unhides all columns outside the table and I have tried to make a small piece of code that hides the

Sub ShowHidden()


Rows.Hidden = False
Columns.Hidden = False
End Sub

Sub HideEmptyColumns()

  ActiveSheet.ListObjects("Table1").Columns.Hidden = True
End Sub

后者失败

是否可以在excel中编写隐藏表中空列的代码，如果这样，代码看起来如何

Is it possible to make code that hides empty columns in a table in excel and if so, how does the code look

亲切的问候

推荐答案

需要使用 Range.SpecialCells方法(Excel)

Need to use the properties of the ListObject object (Excel), in this case use the DataBodyRange to set the range to loop after. You'll also need to use Range.SpecialCells method (Excel)

Dim lo As ListObject
    Set lo = ThisWorkbook.Worksheets("DATA").ListObjects("lo.Data")     'change as required

    For Each rCol In lo.DataBodyRange.Columns
       '… Here goes the validation of the cells within the column (i.e. rCol)
       If … Then rCol.EntireColumn.Hidden = True
    Next

要取消隐藏列，请使用:

To unhide the columns use:

Dim lo As ListObject
    Set lo = ThisWorkbook.Worksheets("DATA").ListObjects("lo.Data")     'change as required
    lo.DataBodyRange.EntireColumn.Hidden = False

这篇关于使用VBA在Excel中隐藏表格中的空列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！