Copy filtered data

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

Try Advanced Filter, excellent tutorial here from Debra Dalgeish, owner of
the site,
http://www.contextures.com/xladvfilter01.html#ApplyAF
Regards,
Alan.
"Hans Knudsen" <[email protected]> wrote in message
news:%[email protected]...
> 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
>

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 wrote:
>
> 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

--

Dave Peterson

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

Hans Knudsen


"Dave Peterson" <[email protected]> skrev i en meddelelse news:[email protected]...
> 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 wrote:
>>
>> 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
>
> --
>
> Dave Peterson