VBA copy range after autofilter

0
votes

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
1
vbaexcel
Welcome to Stack Overflow! Check out minimal reproducible example, and if the answer below does not solve your problem, please share your code illustrating the "several ways" that you have tried and also please define "did not work" - ashleedawg
stackoverflow.com/a/36672957/4539709 - 0m3r

1 Answers

2
votes

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