仅可见单元格的行数 [英] row count for visible cells only

问题描述

我想创建一个宏，该宏将用公式填充空白单元格.该公式将引用上面的单元格，但是仅在将 Subtotal 应用于我的数据之后，并且仅在具有 Total 的行上进行(请参见下面的示例屏幕截图):

I want to create a macro that will fill in blank cells with a formula. The formula will reference the cell above, but only after I apply Subtotal to my data, as well as only on the rows with a Total (see below Sample screenshot):

到目前为止，我的宏将应用小计，然后在具有总计的列上进行过滤，并在单元格中过滤出具有 Total 的任何内容.然后，它计算所有不可见的行并减去2(我不想计算标题和总计).它需要计数并循环公式应用程序.

So far my macro will apply the subtotal, then filter on the column with the totals and filter for anything with Total in the cell. Afterwards it counts all the invisible rows and subtracts 2 (I do not want to count the header and grand total). It takes the count and loops the formula application.

也就是说，它在大多数情况下都有效，但是每隔很多次它都会错误地计数2，而我不知道为什么.

That said, it works the majority of the time, but every so often it miscounts by 2 and I can't figure out why.

数据的格式相同，并且每次使用相同的列.

The data is formatted the same and the same columns are used each time.

我希望我能清楚地解释我的问题，并且样本和代码段就足够了:

I hope I explained my problem clearly and the sample and code snippet is enough:

'filling in empty cells on subtotal line
Cells.Select
    Selection.AutoFilter
    LastRow = Range("G" & Rows.Count).End(xlUp).Row
        x = LastRow
    ActiveSheet.Range("G1:G" & x).AutoFilter Field:=7, Criteria1:="=*total*", Operator:=xlAnd, Criteria2:="<>Grand Total", Operator:=xlAnd

Set rng = ActiveSheet.AutoFilter.Range
'-2 is to NOT count the header or Grand Total for my loop count
RowCount = rng.Columns(2).SpecialCells(xlCellTypeVisible).Count - 2
    r = RowCount

推荐答案

感谢Doktor，但我环顾了一下，发现这种解决方案可以解决我的问题，它总是计算要循环的正确数字.

Thanks Doktor but I looked around some more and found this solution that worked for my problem, it always counts the correct number to loop.

    Dim LastRow as interger, x as integer

    'add autofilter
    Cells.Select
    Selection.AutoFilter
    'filter for Total
    ActiveSheet.Range("G1:G" & x).AutoFilter Field:=7, Criteria1:="=*total*", Operator:=xlAnd, Criteria2:="<>Grand Total", Operator:=xlAnd

    'find last row
    LastRow = Range("G" & Rows.Count).End(xlUp).Row
       x = LastRow

    'count visible rows
    RowCount = Range("G1:G" & x).Rows.SpecialCells(xlCellTypeVisible).Count - 2
       r = RowCount

这篇关于仅可见单元格的行数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！