After filtering rows and selecting some of those cells, I used this vba code for setting the range of selection to rng

Dim rng As Range
Set rng = Selection

But selection contains the cells that filtered, And I don't want them. How can I fix this?

1 Answers

0
Community On

Sample Given Data:
1. Unfiltered data range with header is A1:J20.
2. Row 1 is header.
3. Column 2 or Column B Header = "Last Name"

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