Solved: Copy only filtered rows

[sasa]

Hi all,
I am looking for same code to copy only the filtered rows of a column, after the use of the Excel filter.
Infact if one use the usual copy and paste option this selects all the rows of the column and not only the filtered rows.

Thanks for any help

Sasa

[Bob Phillips]

You need Specialcells(xlCellTypeVisible).

What is the column being filtered, and what is being copied?

[david000]

You can also filter then select the data you need and press.

Alt + ;


The Alt key and the semicolon that will also select just thte data you need or as xld said Visible Cells Only.

[sasa]

Well, for examplea
A1= a
B1= b
C1 = c
d1 = a

If I filter by a
then the filterd rows are only a1 and d1 (two rows) but if I use copy ad past on these two showed rows excel does not let me to copy only these filtered rows but I have to copy and past the four rows at all.

Thanks

[Simon Lloyd]

Try this:

Sub Copy_filtered_range()
Dim Rng As Range
Set Rng = Cells.SpecialCells(xlCellTypeVisible)
Rng.Copy Destination:=Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
End Sub
It copies what is visible after you have filtered and pastes it to the next available row in sheet 2.

[sasa]

Your solution is great !
Thanks

sasa