Range Selection 2

[max1565]

Say I use autofilter on column a on sheet1. I would then like to copy the results to column b on sheet two. How do I select the range of the filtered to copy? The first cell will always be a2 on sheet1 but how do you code to find the last cell? I tried using ctrldown arrow, but it does not select the cells for copying.

Thanks in advance

 

[Zathras]

Use .SpecialCells property of a range. Um, you did define a range name for your data area, didn't you?
[blue]

Sub copydata()
  Range("database").SpecialCells(xlCellTypeVisible).Copy _
      Destination:=Worksheets("Sheet2").Range("B1")
End Sub

[/color]
You may want to clear the destination area first, in case the current filter is showing fewer rows than the last time.

 

[TonyJollans]

Hi max1565,

Not sure if you want code here or not.

<Ctrl><SHIFT><Down Arrow> is what you want.

In code, after you have applied your filter, ..

Range([A2], [A2].End(xlDown)).Select

.. should select only the filtered cells.

Enjoy,
Tony

 

[max1565]

thanks