selecting visible rows

redirect

New Member
Joined
Sep 17, 2002
Messages
5
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?
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
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
 
Upvote 0
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.
 
Upvote 0
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>
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,037
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top