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?
 

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Flenley

Board Regular
Joined
Jul 31, 2002
Messages
71
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

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

MrExcel MVP
Joined
Feb 10, 2002
Messages
4,209
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

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

Watch MrExcel Video

Forum statistics

Threads
1,122,483
Messages
5,596,405
Members
414,064
Latest member
Duncthegreat

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
Top