selecting visible rows

[redirect]

I'm using a macro to select the visible cells from a filtered list. I have a list of dozens of items across several columns and I filter it down to one row of cells. When I copy the one visible row to a destination on another worksheet, the visible row and two rows up are pasting as well. This includes the column label and one row higher. I only want to copy and paste the visible row with meaningful information. Here's what I'm using;
Set rng1 = rng1.SpecialCells(xlVisible)
Can anyone help?

 

[Flenley]

Not a VBA answer but you can switch to visible cells only, so when you copy and paste info the hidden cells do not go across.

To go visible cells only, click on the edit menu, then the select go to, then select special, then select visible cells only.

If you record yourself doing this then you should get the properties you need.

Hope this helps

Angus

 

[redirect]

I've tried it. I want the filtered row to copy and paste to another worksheet automatically so I don't want to manually copy and paste. I need a macro to do this. The problem is the selecting visible cells. The header row is visible and is copy and pasted as well. There's probably a simple way to literally copy and paste just the filtered row and not visible cells. Thanks for the information though. I do appreciate it.

 

[Ivan F Moala]

On 2002-09-18 20:17, redirect wrote:
I've tried it. I want the filtered row to copy and paste to another worksheet automatically so I don't want to manually copy and paste. I need a macro to do this. The problem is the selecting visible cells. The header row is visible and is copy and pasted as well. There's probably a simple way to literally copy and paste just the filtered row and not visible cells. Thanks for the information though. I do appreciate it.

What you need to do is set your rg to the filtered range BUT offset one row down eg
Assuming Filtered row is C1:C17


<pre/>
Sub Macro1()
Dim Rg As Range

'// Filtered rg with header = C1:C17
'// Just select one row down
Set Rg = Range("C2:C16")

'// Assumes Range is Filtered so now get the result

Set Rg = Rg.SpecialCells(xlCellTypeVisible)
Rg.Copy Sheet2.[A1]

End Sub
</pre>

 

[redirect]

Thank you. That did the trick. I've struggled with this for a few days so I'm relieved to have a workable solution. Thanks again!