select a range on VISIBLE cells in a column

[keith]

I need a macro to select and copy a range (or otherwise define a range) in a single column of a list excluding hidden rows. I can't manually define the Cells because the hidden rows change based on filters. So AFTER the list is filtered it hides a bunch of rows based on the criteria of the filter. I need to copy say the 1st 10 VISIBLE cells in column "C" after the filter. Let me know if I can clarify more.. thanks in advance

 

[Ian Mac]

On 2002-09-03 12:32, keith wrote:
I need a macro to select and copy a range (or otherwise define a range) in a single column of a list excluding hidden rows. I can't manually define the Cells because the hidden rows change based on filters. So AFTER the list is filtered it hides a bunch of rows based on the criteria of the filter. I need to copy say the 1st 10 VISIBLE cells in column "C" after the filter. Let me know if I can clarify more.. thanks in advance

Is the range you need just the 1 Column in width, or does this problem expand?

if it is in Column C and you had C1:C1000 as the range (unfiltered 1000rows below the field name).
Then you can just use the range, i.e.

Sub CopyFilter()

Range("C1:C1000").Copy
Sheets(2).Select
Range("a1").PasteSpecial Operation:=xlPasteSpecialOperationNone

End Sub

will copy the range visable to to sheet2 a1 and down,
as copying the range (inc. the hidden rows) will just copy the visable cells.

 

[Ian Mac]

I have just re-read your post, sorry, I see you need only the first 10 visable.
BUT, the copy part should work as it does ignore blanks.
I'll have think, someone will doubt less get here first.

 

[Ian Mac]

But here a big old cheat:

Sub CopyFilter()

Range("C1:C1000").Copy
Sheets(2).Range("a1").PasteSpecial Operation:=xlPasteSpecialOperationNone
Sheets(2).Range("a12:a1000").Delete
End Sub

 

[keith]

I think I might can make this work or at least you have me on the right track..

Thanks