CODEWITHSUNDEEP
vbaexcel
Gamze Ural
Gamze Ural

Reputation: 47

VBA copy range after autofilter

I would like to copy the table after autofilter function works as below code. I try several ways but did not work. How can I filter the only visible cells(table) after autofilter ?

ActiveSheet.Range("$K$9:$K$1000").AutoFilter Field:=1, Criteria1:="Hatalı", Operator:=xlAnd

Upvotes: 0

Views: 1887

Answers (1)

Pᴇʜ
Pᴇʜ

Reputation: 57683

You can use the Range.SpecialCells Method to get only visible cells in a range.

ActiveSheet.Range("$K$9:$K$1000").SpecialCells(xlCellTypeVisible).Copy Worksheets("destination").Range("A1")

Note: this will throw an error if no cells are visible in this range. So you might want to check that first before you copy.

Dim FilteredRange As Range

On Error Resume Next 'disable error reporting, below line throws an error if no cells are visible in that range
Set FilteredRange = ActiveSheet.Range("$K$9:$K$1000").SpecialCells(xlCellTypeVisible)
On Error GoTo 0 'enable error reporting

If Not FilteredRange Is Nothing Then
    'copy filtererd range if there are visible cells
    FilteredRange.Copy Worksheets("destination").Range("A1")
Else
    MsgBox "The filtered range is empty"
End If

Upvotes: 2

Related Questions