Обходной путь для фильтрации по нескольким критериям

Question

Обходной путь для фильтрации по нескольким критериям

Принятый ответ обеспечивает отличную информацию, но здесь, похоже, есть несколько других морщин - различия между пониманием списка и выражениями генератора. Демонстрация, с которой я играл:

class Foo:

    # A class-level variable.
    X = 10

    # I can use that variable to define another class-level variable.
    Y = sum((X, X))

    # Works in Python 2, but not 3.
    # In Python 3, list comprehensions were given their own scope.
    try:
        Z1 = sum([X for _ in range(3)])
    except NameError:
        Z1 = None

    # Fails in both.
    # Apparently, generator expressions (that's what the entire argument
    # to sum() is) did have their own scope even in Python 2.
    try:
        Z2 = sum(X for _ in range(3))
    except NameError:
        Z2 = None

    # Workaround: put the computation in lambda or def.
    compute_z3 = lambda val: sum(val for _ in range(3))

    # Then use that function.
    Z3 = compute_z3(X)

    # Also worth noting: here I can refer to XS in the for-part of the
    # generator expression (Z4 works), but I cannot refer to XS in the
    # inner-part of the generator expression (Z5 fails).
    XS = [15, 15, 15, 15]
    Z4 = sum(val for val in XS)
    try:
        Z5 = sum(XS[i] for i in range(len(XS)))
    except NameError:
        Z5 = None

print(Foo.Z1, Foo.Z2, Foo.Z3, Foo.Z4, Foo.Z5)

0

excel vba

задан Valkeif 20 February 2019 в 15:32

2 ответа

Другие вопросы по тегам:

excel vba

Похожие вопросы:

score 0 · Answer 1

Я изменил ваш код, чтобы гарантировать, что все переменные объявлены.

Sub HideRows()
'Ensure you declare all your variables
Dim ws1 As Worksheet, ws2 As Worksheet, lRow As Long
Dim ColumnOne As Integer, ColumnTwo As Integer
Dim dRng As Range, eRng As Range
Dim dRngCnt As Long, eRngCnt As Long

'Assign worksheets and variables
Set ws1 = ThisWorkbook.Sheets("Sheet1")
Set ws2 = ThisWorkbook.Sheets("Sheet2")

'Identifying the specific range as variable
Set dRng = ws2.Range("D2:D9")
Set eRng = ws2.Range("E2:E9")

'Assigning a variable to the countA will simplify your IF and ELSEIF statements
dRngCnt = Application.WorksheetFunction.CountA(dRng)
eRngCnt = Application.WorksheetFunction.CountA(eRng)

    With ws1
        ColumnOne = .Cells(1, .Columns.Count).End(xlToLeft).Column
        'I replaced "lastColumn" with "ColumnOne", because they are the same value, so you only need to use one
        lRow = .Cells(.Rows.Count, ColumnOne).End(xlUp).Row
        ColumnTwo = ColumnOne - 1

        If dRngCnt <> 0 Then 'I use the countA variable for column D Range
            For i = 1 To lRow
                If .Cells(i, ColumnTwo).Value = 0 Then 'Any 0s in the second to last columns will hide the row
                    .Rows(i).EntireRow.Hidden = True
                End If
            Next i

        ElseIf eRngCnt <> 0 Then 'I use the countA variable for column E Range
            For i = 1 To lRow
                If .Cells(i, ColumnOne).Value = 0 Then
                    .Rows(i).EntireRow.Hidden = True
                End If

            Next i
        Else
            Exit Sub

        End If
    End With

End Sub

score 0 · Answer 2

Код ниже, кажется, работает сейчас:

With ws1

    ColumnTwo = .Cells(1, .Columns.Count).End(xlToLeft).Column
    LastRow = .Cells(.Rows.Count, lastColumn).End(xlUp).Row

    ColumnOne = ColumnTwo - 1


    With ws2
    Set aDataRange = ws2.Range("D47:D61")
    Set pDataRange = ws2.Range("E47:E61")
        If Application.WorksheetFunction.CountA(aDataRange) <> 0 Then
            MsgBox ("ColumnOne")
            With ws1
                For i = 1 To LastRow
                    If .Cells(i, ColumnOne).Value = 0 Then
                        .Rows(i).EntireRow.Hidden = True
                    End If

                Next i
            End With

        ElseIf Application.WorksheetFunction.CountA(pDataRange) <> 0 Then
            MsgBox ("ColumnTwo")
            With ws1
                For i = 1 To LastRow
                    If .Cells(i, ColumnTwo).Value = 0 Then
                        .Rows(i).EntireRow.Hidden = True
                    End If

                Next i
            End With
        Else
            Exit Sub
        End If
    End With
End With

Почему мне нужно определить области диапазона для D47: D61 / E47: E61 на ws2 отдельно? Почему бы «ws2.Application.WorksheetFunction.CountA (« Range »)» не применяться к диапазону на ws2, даже если он содержался в «With ws2»?