Excel(VBA)에서 고급 필터를 적용한 후 표시되는 행의 범위를 가져오는 방법

Excel(VBA)에서 고급 필터를 적용한 후 표시되는 행의 범위를 가져오는 방법

다음은 Sheet2(기준 범위)의 값 범위를 사용하여 Sheet1 워크시트의 열 A에 고급 필터를 적용하는 코드입니다.

Range("A1:A100").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
        Sheets("Sheet2").Range("A1:A10"), Unique:=False

이 코드를 실행한 후 현재 화면에 표시되는 행을 처리해야 합니다.

현재 다음과 같은 코드를 사용하고 있습니다.

For i = 1 to maxRow
   If Not ActiveSheet.Row(i).Hidden then
     ...do something that I need to do with that rows
   EndIf
Next

고급 필터를 적용한 후 다양한 행을 볼 수 있는 간단한 속성이 있습니까?

ActiveSheet.Range("A1:A100").Rows.SpecialCells(xlCellTypeVisible)

이것은 다음을 산출합니다.Range물건.

랜스의 해결책은 대부분의 상황에서 효과가 있을 것입니다.

그러나 대규모/복잡한 스프레드시트를 처리할 경우 "특수 셀 문제"가 발생할 수 있습니다.간단히 말해서, 만들어진 범위가 8192개 이상의 비연속 영역을 야기하는 경우(이 경우가 발생할 수 있음) Excel은 SpecialCells에 액세스하려고 할 때 오류를 발생시키고 코드는 실행되지 않습니다.워크시트가 이 문제에 직면할 정도로 복잡할 경우 루프 방식을 사용하는 것이 좋습니다.

이 문제는 SpecialCells 속성 자체의 문제가 아니라 Range 개체의 문제입니다.이는 매우 복잡할 수 있는 범위 개체를 얻으려고 할 때마다 오류 처리기를 사용하거나 이미 수행한 대로 해야 한다는 것을 의미합니다. 즉, 범위의 각 요소에서 프로그램이 작동하도록 합니다(범위를 위로 분할).

가능한 또 다른 방법은 Range Objects 배열을 반환한 다음 배열을 순환하는 것입니다.가지고 놀 수 있는 몇 가지 예제 코드를 게시했습니다.그러나 문제가 설명될 것으로 예상되거나 코드가 견고하다는 확신을 얻고자 하는 경우에만 이 문제를 해결해야 합니다.그렇지 않으면 불필요한 복잡성일 뿐입니다.

Option Explicit

Public Declare Function GetTickCount Lib "kernel32" () As Long

Public Sub GenerateProblem()
    'Run this to set up an example spreadsheet:
    Dim row As Long
    Excel.Application.EnableEvents = False
    Sheet1.AutoFilterMode = False
    Sheet1.UsedRange.Delete
    For row = 1 To (8192& * 4&) + 1&
        If row Mod 3& Then If Int(10& * Rnd)  7& Then Sheet1.Cells(row, 1&).value = "test"
    Next
    Sheet1.UsedRange.AutoFilter 1&, ""
    Excel.Application.EnableEvents = True
    MsgBox Sheet1.UsedRange.SpecialCells(xlCellTypeVisible).address
End Sub

Public Sub FixProblem()
    'Run this to see various solutions:
    Dim ranges() As Excel.Range
    Dim index As Long
    Dim address As String
    Dim startTime As Long
    Dim endTime As Long
    'Get range array.
    ranges = GetVisibleRows
    'Do something with individual range objects.
    For index = LBound(ranges) To UBound(ranges)
        ranges(index).Interior.ColorIndex = Int(56 * Rnd + 1)
    Next

    'Get total address if you want it:
    startTime = GetTickCount
    address = RangeArrayAddress(ranges)
    endTime = GetTickCount
    Debug.Print endTime - startTime, ; 'Outputs time elapsed in milliseconds.

    'Small demo of why I used a string builder. Straight concatenation is about
    '10 times slower:
    startTime = GetTickCount
    address = RangeArrayAddress2(ranges)
    endTime = GetTickCount
    Debug.Print endTime - startTime
End Sub

Public Function GetVisibleRows(Optional ByVal ws As Excel.Worksheet) As Excel.Range()
    Const increment As Long = 1000&
    Dim max As Long
    Dim row As Long
    Dim returnVal() As Excel.Range
    Dim startRow As Long
    Dim index As Long
    If ws Is Nothing Then Set ws = Excel.ActiveSheet
    max = increment
    ReDim returnVal(max) As Excel.Range
    For row = ws.UsedRange.row To ws.UsedRange.Rows.Count
        If Sheet1.Rows(row).Hidden Then
            If startRow  0& Then
                Set returnVal(index) = ws.Rows(startRow & ":" & (row - 1&))
                index = index + 1&
                If index > max Then
                    'Redimming in large increments is an optimization trick.
                    max = max + increment
                    ReDim Preserve returnVal(max) As Excel.Range
                End If
                startRow = 0&
            End If
        ElseIf startRow = 0& Then startRow = row
        End If
    Next
    ReDim Preserve returnVal(index - 1&) As Excel.Range
    GetVisibleRows = returnVal
End Function

Public Function RangeArrayAddress(ByRef value() As Excel.Range, Optional lowerindexRV As Variant, Optional upperindexRV As Variant) As String
    'Parameters left as variants to allow for "IsMissing" values.
    'Code uses bytearray string building methods to run faster.
    Const incrementChars As Long = 1000&
    Const unicodeWidth As Long = 2&
    Const comma As Long = 44&
    Dim increment As Long
    Dim max As Long
    Dim index As Long
    Dim returnVal() As Byte
    Dim address() As Byte
    Dim indexRV As Long
    Dim char As Long
    increment = incrementChars * unicodeWidth 'Double for unicode.
    max = increment - 1& 'Offset for array.
    ReDim returnVal(max) As Byte
    If IsMissing(lowerindexRV) Then lowerindexRV = LBound(value)
    If IsMissing(upperindexRV) Then upperindexRV = UBound(value)
    For index = lowerindexRV To upperindexRV
        address = value(index).address
        For char = 0& To UBound(address) Step unicodeWidth
            returnVal(indexRV) = address(char)
            indexRV = indexRV + unicodeWidth
            If indexRV > max Then
                max = max + increment
                ReDim Preserve returnVal(max) As Byte
            End If
        Next
        returnVal(indexRV) = comma
        indexRV = indexRV + unicodeWidth
        If indexRV > max Then
            max = max + increment
            ReDim Preserve returnVal(max) As Byte
        End If
    Next
    ReDim Preserve returnVal(indexRV - 1&) As Byte
    RangeArrayAddress = returnVal
End Function

Public Function RangeArrayAddress2(ByRef value() As Excel.Range, Optional lowerIndex As Variant, Optional upperIndex As Variant) As String
    'Parameters left as variants to allow for "IsMissing" values.
    'Code uses bytearray string building methods to run faster.
    Const incrementChars As Long = 1000&
    Const unicodeWidth As Long = 2&
    Dim increment As Long
    Dim max As Long
    Dim returnVal As String
    Dim index As Long
    increment = incrementChars * unicodeWidth 'Double for unicode.
    max = increment - 1& 'Offset for array.
    If IsMissing(lowerIndex) Then lowerIndex = LBound(value)
    If IsMissing(upperIndex) Then upperIndex = UBound(value)
    For index = lowerIndex To upperIndex
        returnVal = returnVal & (value(index).address & ",")
    Next
    RangeArrayAddress2 = returnVal
End Function

다음 코드를 사용하여 표시 가능한 셀 범위를 가져올 수 있습니다.

Excel.Range visibleRange = Excel.Application.ActiveWindow.VisibleRange

이게 도움이 되길 바랍니다.

언급URL : https://stackoverflow.com/questions/1370286/how-to-get-the-range-of-the-visible-rows-after-applying-an-advanced-filter-in-ex