Copy filtered data

[Hans Knudsen]

Let's say I have data in A1:Gxx. Now I use Autofilter to find all rows which has a "2" in column C. Let's say it leaves rows 1:4 and
8:10. Now I want to copy the filtered data in columns F:G and paste the values (not to an empty range which is easy) but to the same
cells in colums D:E.

Any help?

Hans Knudsen

 

[Alan]

Try Advanced Filter, excellent tutorial here from Debra Dalgeish, owner of
the site,
http://www.contextures.com/xladvfilter01.html#ApplyAF
Regards,
Alan.

 

[Dave Peterson]

This assumes that you applied the filter to the worksheet.

Option Explicit
Sub testme()
Dim myRng As Range
Dim myArea As Range

With Worksheets("sheet1")
If .AutoFilter.Range.Columns(1).Cells _
.SpecialCells(xlCellTypeVisible).Cells.Count = 1 Then
'only the header row showing.
'do nothing
Else
With .AutoFilter.Range
Set myRng = Intersect(.Resize(.Rows.Count - 1) _
.Offset(1, 0).Cells.SpecialCells(xlCellTypeVisible), _
.Parent.Range("F:G"))
For Each myArea In myRng.Areas
myArea.Offset(0, -2).Value = myArea.Value
Next myArea
End With
End If
End With

End Sub

 

[Hans Knudsen]

Just what I wanted!
Thank you very much Dave Peterson.

Hans Knudsen