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" wrote in message
...
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" skrev i en meddelelse ...
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