一致的方法来检查整个范围是否被隐藏 [英] Consistant way to check if entire range is hidden

问题描述

我有一个用于调整过滤器并将唯一值读入组合框等的扩展代码，它几乎可以正常运行，但是有时这行代码有时无法正确识别(仅当我对某些值进行过滤时才出现).如果由于通过makro应用了过滤器而导致所有行都被隐藏，则可以防止发生错误.

I have an extensive code for adjusting filters and reading the unique values into comboboxes etc. and it's working almost perfectly, however this one line of code is sometimes not identified correctly (only when I filter for certain values though it seems). It is supposed to prevent errors in case all the rows are hidden due to the filters that have been applied through the makro.

If RangeCombo.EntireRow.Hidden = False Then

RangeCombo在工作时和不工作时都包含隐藏和未隐藏的行，因此我真的不知道是什么原因引起的.我试过了:

RangeCombo contains both hidden and unhidden rows when it works and when it doesn't, so I really don't know what could be causing this. I tried this:

If RangeCombo.SpecialCells(xlCellTypeVisible).Count > 0 Then

但是如果范围内没有可见的单元格，则会引发错误.

but it throws up an error if there are no visible cells in the range.

我也尝试过:

    Set x = RangeCombo.Find("*", , xlFormulas, xlWhole)
    If Not x Is Nothing Then

但这会找到单元格，即使整个范围都被隐藏了...

but that will find cells, even if the entire range is hidden...

对于任何可以帮助我解决xlCellTypeVisible错误(当RangeCombo中的所有单元格都为空时)或任何其他可能实现我的目标的解决方案，我都会感到满意.

I'd be happy for any solutions that might help me circumnavigate the xlCellTypeVisible error (when all cells in RangeCombo are empty) or any other way I might achieve my goal.

推荐答案

在检查自动过滤的单元格时，始终将标头行包含在范围内，并检查可见单元格的数量是否大于1，如下所示...

While checking the autofiltered cells, always include the header row in the range and check if the visible cells count is greater than 1 like below...

If Range("A1:A" & LastRow).SpecialCells(xlCellTypeVisible).Count > 1 Then

LastRow是表格上数据的最后一行.根据您的要求更改范围.

Where LastRow is the last row with data on the sheet. Change the range as per your requirement.

如果在应用自动过滤器后未返回任何行，则该计数将等于1，因为只有标头行可见，并且您可以跳过对可见行/单元格执行一些操作.

If no row is returned after applying autofilter, the count would be equal to 1 as only header row would be visible and you can skip doing some actions on visible rows/cells.

这篇关于一致的方法来检查整个范围是否被隐藏的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！