Solved: Question regarding Filter function

[Peskarik]

This is only partly a VBA question.

Assume:
on one sheet you have two columns, like this:
1 10
2 0
3 20
4 0
5 30

Now I mark the row above the two columns and use Filter button to filter away those observations that contain 0 in second column. Now I want to copy the remaining 3x2 array and paste it somewhere else.
When I do this copypaste, I get back the initial 5x2 array!
What should I do to get only the 3x2 array.
I have Office 2007. In Office 2003 it works.

[Peskarik]

Disregard this thread.

[Oorang]

For interface: Select filtered text. Press alt-semicolon to select only visible text, then copy. (Note the interface usually will only copy visible cells by default, but I have noticed this problem in 97. I thought it was corrected in later versions.)

For vba you can use the special cells modifier[vba]Sub Foo()
Dim rng As Excel.Range
Set rng = Selection
Set rng = rng.SpecialCells(xlCellTypeVisible)
MsgBox rng.Address
End Sub[/vba]
(Note: If your data is framented to the point that SpecialCells yields more than 8192 non-contiguous cells then SpecialCells throws an exception. If you encounter this, you may have to go for a more more indepth solution.)

Edit: Whups... Missed the disregard post.

[Peskarik]

THanks, Aaron!