PDA

View Full Version : Solved: Question regarding Filter function



Peskarik
02-01-2008, 07:26 AM
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! :banghead:
What should I do to get only the 3x2 array.
I have Office 2007. In Office 2003 it works. :think: :help

Peskarik
02-01-2008, 07:36 AM
Disregard this thread.

Oorang
02-01-2008, 07:39 AM
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 modifierSub Foo()
Dim rng As Excel.Range
Set rng = Selection
Set rng = rng.SpecialCells(xlCellTypeVisible)
MsgBox rng.Address
End Sub
(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
02-03-2008, 09:22 AM
THanks, Aaron!