根据不同的列数据范围隐藏行 [英] Hiding Rows Based On Different Column Data Ranges

问题描述

我对使用VBA相当陌生，正在尝试创建一个代码，该代码将查看具有不同数据范围的两个不同列，并隐藏最后一个数据点(引用两个列)之后的行.

I am fairly new to using VBA and am trying to create a code that will look at two different columns with varying data ranges and hide rows beyond the last data point (referencing both columns).

此刻我有这个

    Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
'Updateby Extendoffice 20160913
    Dim xRg As Range
    Application.ScreenUpdating = False
        For Each xRg In Range("G24:G71, N24:N71")
            If xRg.Value = "" Then
                xRg.EntireRow.Hidden = True

            Else
                xRg.EntireRow.Hidden = False
            End If
        Next xRg
    Application.ScreenUpdating = True
End Sub

G列和N列是两个单独的数据透视表中的人名.因此，根据日期的不同，每个列中的数据范围可能会有所不同(数据透视表具有不同的过滤器).例如，今天在G列中可能有50行数据，而在N列中可能有40行数据.在这种情况下，上述公式将起作用并隐藏没有数据的第51至71行.但是，如果G列有40行数据，而ColumnN有50行，那么它将引用G列并隐藏41-71行，从而从N列中隐藏不需要的数据.

Column G and Column N are peoples names in two separate pivot tables. So depending on the day the range of data in each of these columns can differ (the pivot table has different filters). For example today there could be 50 rows of data in Column G and 40 in Column N. In this case the above formula would work and hide rows 51 to 71 with no data in. However, if Column G has 40 rows of data and Column N has 50 rows then it would reference column G and hide rows 41 - 71, hiding unwanted data from column N.

有没有一种方法来获取代码以查看列G&N，确定哪个具有更大的数据范围，并隐藏超出该点的行.

Is there a way to get the code to look at Columns G & N, identify which has a larger data range and hide rows beyond that point.

在此先感谢您的帮助.

推荐答案

尝试以下方法:

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
    Dim xRg As Range, xCell As Range, bHide As Boolean
    Application.ScreenUpdating = False
        For Each xRg In Range("G24:G71, N24:N71").Rows
            bHide = True
            For Each xCell In xRg.Cells
                If IsEmpty(xRg.Value) = False Then
                    bHide = False
                End If
            Next xCell
            xRg.EntireRow.Hidden = bHide
        Next xRg
    Application.ScreenUpdating = True
End Sub

错误的解释:您的代码遍历所有单元格，并根据单个单元格做出决定.这是不正确的，因为您想知道一行中的两个单元格是否都为空.

Explanation of the error: your code iterates through all cells, and makes a decision based on individual cells. That is incorrect because you want to know if both cells in a row are empty.

解决方案:因此，您应该在外循环中迭代行，并在内循环中迭代给定行内的单元格.请注意是否有任何非空单元格，并根据此决定是否隐藏行.

Solution: So you should iterate rows in an outer loop, and cells inside the given row in an inner loop. Take note if there is any cell that is non-empty, and make the decision on hiding the row based on this.

更新

对不起，我的代码无法正常工作，因为 Range("G24:G71，N24:N71")由2个 .Areas 组成，尽管 .Rows.Count 返回48， For Each 枚举96个行"，每个行由1个单元格组成(每个区域48行).

Sorry, my code did not work because Range("G24:G71, N24:N71") consists of 2 .Areas, and although .Rows.Count returns 48, For Each enumerates 96 "rows", each consisting of 1 cell (48 rows for each area).

我修改了代码以考虑区域:

I modified the code to take into account Areas:

Private Sub Worksheet_PivotTableUpdate()
    Application.ScreenUpdating = False
    With Range("G24:G71,N24:N71")
        Dim r As Long: For r = 1 To .Areas(1).Rows.Count
            Dim bHide As Boolean: bHide = True
            Dim xArea As Range: For Each xArea In .Areas
                If IsEmpty(xArea.Cells(r, 1).Value) = False Then
                    bHide = False
                End If
            Next xArea
            .Rows(r).EntireRow.Hidden = bHide
        Next r
    End With
    Application.ScreenUpdating = True
End Sub

这篇关于根据不同的列数据范围隐藏行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！