MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Autofilter question

  Asked By: Jawna    Date: Dec 04    Category: MS Office    Views: 802

I am trying to autofilter a large number of rows and then copy the
result of the filtering to a new range. I assumed that the following
code would do this nicely, but it doesn't.

It wants me to specify a range of cell equal to that resulting from
the filter operation rather than just the first cell where i want
the extracted info to start. Is there a way round this?

Sub Macro2()
Range("A1").CurrentRegion.AutoFilter _
Field:=3, Criteria1:="25.0000"
ActiveSheet.Cells.SpecialCells(xlCellTypeVisible).Rows.Copy _
End Sub



2 Answers Found

Answer #1    Answered By: Stefan Thompson     Answered On: Dec 04

try this batch of code...found it on the site:

it also has a bunch of other code regarding autofilters...haven't really
read too deep into this page...but i hope it was what you needed...

Sub CopyFilter()
'by Tom Ogilvy
Dim rng As Range
Dim rng2 As Range

With ActiveSheet.AutoFilter.Range
On Error Resume Next
Set rng2 = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
On Error GoTo 0
End With
If rng2 Is Nothing Then
MsgBox "No data to copy"
Set rng = ActiveSheet.AutoFilter.Range
rng.Offset(1, 0).Resize(rng.Rows.Count - 1).Copy _
End If

End Sub

Answer #2    Answered By: Ivan Coleman     Answered On: Dec 04

Destination range  can not accomodate origin range, i.e destination range is
from AA2 to lastcolumn, but origin range is the entire row.

ActiveSheet.Cells.SpecialCells(xlCellTypeVisible).Rows.Copy _
Destination:=Range("A1") <====


ActiveSheet.UsedRange.Cells.SpecialCells(xlCellTypeVisible).Rows.Copy _ <<====

Not tested.

Didn't find what you were looking for? Find more on Autofilter question Or get search suggestion and latest updates.