CODEWITHSUNDEEP
excelvbarangeautofilter
David
David

Reputation: 1191

Excel vba Autofilter top 10 rows, copy result based on criteria

I have data in my spreadsheet in range A6:H105

I want to filter the first ten rows (A6:H15) with fixed criteria on the H-column and copy only column A, D, E from result

So far I have the following code:

Sub Filter()
    ActiveSheet.Range("A6:H15").AutoFilter Field:=8, Criteria1:="xxx"
End Sub

Question 1: Why is first row not filtered?

Question 2: How to copy only column A,D,E from range after applied filter?

Upvotes: 0

Views: 1604

Answers (2)

Ivan Tokarev
Ivan Tokarev

Reputation: 101

You can do something like this:

Sub Filter()

   ActiveSheet.Range("A6:H15").AutoFilter Field:=8, Criteria1:="xxx"
   Range("A:A,B:B,D:D").Select
   Selection.SpecialCells(xlCellTypeVisible).Select
   Selection.Copy
   Range("J1").PasteSpecial Paste:=xlPasteValues

End Sub

Upvotes: 0

Marek Stejskal
Marek Stejskal

Reputation: 2708

Answer 1: It is bacuse Autofilter treats the first row as table header. Set the range to start from one row higher, from row 5.

Answer 2: You can copy a combined range like this

Range("A6:A15, D6:D15, E6:E15").Copy

and then paste it into three adjacent columns wherever you like.

You may need to modify the range to select only filtered or non-blank cells first.

Upvotes: 1

Related Questions