Code to select rows by a null cell in a set column

[Dale]

Excel 2003

I have a 3 worksheet workbook (Instructions, Contacts, Import), On the
Contacts worksheet there is 5 columns, Column "A" has a numeric value
of "1" in all cells (Approx 300 Rows that contain data) The other
columns contain text.

This workbook is for a master email list that will allow a user to
select the contacts they want to import into Outlook (on the Contacts
Sheet) by removing the "1" in Column "A"

After the user has removed the "1" from all rows that they do NOT want
imported, then I want them to be able to click a button to have vba
select all rows that have a numeric value of "1", copy & Paste into
Worksheet "Import" starting at cell A2.

This will allow the user to then import worksheet "Import" into
Outlook.

I need help with the code to select, copy, & paste.

Any and All Help Appreciated!
Dale

 

[Dave Peterson]

You could add data|filter to your data.

Then record a macro when you:
Show all the data (just to make sure)
Filter to show only the 1's in column A
Select the rows to copy (avoid the header)
Edit|Copy
Go to A2 of the other sheet
Edit|paste special|values
Back to the original sheet and show all the data.

You could tweak the macro, but I bet it is very close to working.

In xl2007, you need to add one step...
After you filter the data, select the range and hit F5 (edit|goto in xl2003
menus) and click special, then visible cells only.

It won't hurt if you include this extra step in xl2003, either--it'll make the
upgrade to xl2007 a little less painful since you won't have to worry about this
macro working after the upgrade.

 

[Shane Devenshire]

Hi,

Actually, on my copy of 2007 if you select a filtered range and then copy
you only get visible cells, just like in 2003.

 

[Dave Peterson]

I think it depends on a few things.

I saw this response in a different forum: