excel - 如何将筛选的行选择设置为范围

Question

过滤行并选择其中一些单元格后，我使用此 vba 代码将选择范围设置为 rng

Dim rng As Range
Set rng = Selection

但是选择包含过滤的单元格，我不想要它们。我怎样才能解决这个问题？

标签： excelvba

Answer 1

给定数据示例：
1. 带有标题的未过滤数据范围是 A1:J20。
2. 第 1 行是标题。
3.第2列或B列标题=“姓氏”

Sub setRangeResultOfFilter 
    On Error goto errHandler

    'declare variables for ranges
    Dim oRangeHeadNData as Excel.Range 
    Dim oRangeDataAll as Excel.Range
    Dim oRangeDataTgt as Excel.Range


    'check if filter mode is active in the sheet to filter 
    If ActiveSheet.AutoFilterMode = true then
        'deactivate filter mode 
        ActiveSheet.AutoFilterMode =false
    End If
    'activate filter mode 
    Activesheet.Rows(1).Autofilter

    'set to range variables the data ranges
    Set oRangeHeadNData= Activesheet.Range("A1:J20") 'with header
    Set oRangeOfDataAll = Activesheet.Range("A2:J20") 'without header

    'filter the range
   oRangeHeadNData.AutoFilter Field:=2,Criteria1:="Mormont" 'field 2 is last name
    if oRangeHeadNData.Columns(2).SpecialCells(xlCellTypeVisible).Count-1) > 0 then
        'filtered data with results
        set oRangeDataTgt= oRangeDataAll.SpecialCells(xlCellTypeVisible)
    else
        'filtered data with out results
        set oRangeDataTgt =Nothing
    end if

    if Not oRangeDataTgt Is Nothing then
        'insert the rules you want to add
    end if

deActivateFilterMode:
    If ActiveSheet.AutoFilterMode = true then
        'deactivate filter mode 
        ActiveSheet.AutoFilterMode =false
    End If

exitHandler:
    set oRangeHeadNData = Nothing
    set oRangeDataAll = Nothing
    set oRangeDataTgt =Nothing
    Exit Sub

errHandler:
    if err.number <> 0 then
        Msgbox err.description & " " & err.Number, vbOKOnly+vbInformation, "Set to Range the Filtered Results"
        err.clear
    end If
    goto deActivateFilterMode

End Sub