Copy rows (in Excel 2007)

G

gary

My worksheet has 31,000 rows.
FIND ALL located 1,575 empty cells in column C.
I have selected these cells.
Now, how do I copy the 1,575 rows?
 
H

Harlan Grove

gary said:
My worksheet has 31,000 rows.
FIND ALL located 1,575 empty cells in column C.
I have selected these cells.
Now, how do I copy the 1,575 rows?
Click to expand...

Better to use an AutoFilter.

Select the entire range and run the menu command Data > Filter > AutoFilter.
This will put drop-down buttons on the right side of each cell in the top
row of the selected range. Click on the button for col C, select (Blanks)
from drop-down list. This should filter the range so that only the rows in
which the col C cell is blank are displayed. Edit > Copy, which will include
only the visible, filtered rows in the table, move to another worksheet, and
Edit > Paste. This will include the topmost row from your table, which
AutoFilter takes as column headings rather than as the first record. If you
don't want it, delete the top row in the copied, filtered table. Then return
to the original table and run Data > Filter > AutoFilter again to clear the
AutoFilter.
 
O

OssieMac

Hi Harlan,

Don't you also have to got to special->visible cells after selecting and
before you copy if you only want the visible cells.

Regards,

OssieMac
 
H

Harlan Grove

OssieMac said:
Don't you also have to got to special->visible cells after selecting and
before you copy if you only want the visible cells.
Click to expand...
....

Not if you copy from a filtered range. Copying from filtered ranges
implicitly copies only visible cells.
 
D

Dave Peterson

I saw this response in another forum:
The key here is that if you select just the table (or cells in the same rows
as the table) to copy, then you will get the filtered data.

If you select the filtered cells, plus data outside the table (the row
above/below/both) then you will get the entire list (unfiltered).

You can tell what you are going to get by looking at the "running ants"
selection after pressing Ctrl+C. If you see only one area with the running
ants around the outer perimeter, you are going to get the whole table. If
you see several 'mini-selections' inside a larger bold border, then you will
get the filtered data.
Click to expand...
 
O

OssieMac

O.K. I give in and I have now learnt of another difference between xl2007 and
earlier versions.

I have tested Dave's response. In xl2002, it doesn't matter how the data is
selected whether it is just the filtered data or the filtered data plus the
column headers or the filtered data plus a row below the filtered data, none
of the hidden rows are copied. The 'crawling ants' is a good way of seeing
what is going to occur.

However, in xl2007 all rows are copied unless Find & Select-> Goto special->
Visible rows only is used after selection and before copy. So far I haven't
found any options to set which can alter this.


Regards,

OssieMac
 
D

Dave Peterson

I haven't seen an option to change this behavior in xl2007.

But in my simple tests in xl2007, only the visible cells were selected.
 
R

Ron de Bruin

Hi OssieMac

I am curious, can you send me a example private.

In my testing's Excel 2007 is working the same as older Excel versions if you use AutoFilter.
But if you use a Table in 2007 (List in 2003) the active cell must be inside the Table in 2007 or in the List in 2003

There are more people that report problems so I like to see the file if possible.
 
O

OssieMac

Hi Ron,

You have really thrown me now. I created a test workbook just as I did
yesterday with the intention of sending it to you and it all works as you say
it should. Unfortunately I didn't save the test that I used yesterday.

I assure you that I am not telling porkies when I said yesterday that it
copied all cells and the crawling ants were around the outer perimeter of the
selection only.

Selecting the range and then Find & Select->Goto Special->Visible cells only
prior to copy worked.

Because you tell me that more people report problems I now think that there
may be a bug that raises it ugly head intermittently. Just to add a little to
my history of this, I originally believed that it only copied the visible
cells until one day back when I only had xl2002 I experienced a problem where
it copied all the cells. I decided at the time that I must be mistaken in my
belief that it only copied the visible cells and ever since I have always
used the Special Cells->Visible method. With this latest episode I think that
the bug was around pre xl2007.

The test worksheet that I created had random data so that the filtered rows
were not consecutive. The following is a copy of a recorded macro which
demonstrates how I created the worksheet.

If I come across the problem again then I will certainly forward it to you.

Sub Macro1()
'Recorded macro to create test data for AutoFilter
Sheets("Sheet1").Select
Range("A1").Select
ActiveCell.FormulaR1C1 = "Col Head 1"
Selection.AutoFill Destination:=Range("A1:F1"), _
Type:=xlFillDefault
Range("A1:F1").Select
Range("A2").Select
ActiveCell.FormulaR1C1 = "=RANDBETWEEN(0,10)"
Selection.AutoFill Destination:=Range("A2:F2"), _
Type:=xlFillDefault
Range("A2:F2").Select
Selection.AutoFill Destination:=Range("A2:F101"), _
Type:=xlFillDefault
Range("A2:F101").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.AutoFilter
Range("A1:F1").Select
Selection.Font.Bold = True
Columns("A:F").Select
Selection.Columns.AutoFit
Range("A2").Select
End Sub

Regards,

OssieMac
 
R

Ron de Bruin

If I come across the problem again then I will certainly forward it to you.

Please do

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


OssieMac said:
Hi Ron,

You have really thrown me now. I created a test workbook just as I did
yesterday with the intention of sending it to you and it all works as you say
it should. Unfortunately I didn't save the test that I used yesterday.

I assure you that I am not telling porkies when I said yesterday that it
copied all cells and the crawling ants were around the outer perimeter of the
selection only.

Selecting the range and then Find & Select->Goto Special->Visible cells only
prior to copy worked.

Because you tell me that more people report problems I now think that there
may be a bug that raises it ugly head intermittently. Just to add a little to
my history of this, I originally believed that it only copied the visible
cells until one day back when I only had xl2002 I experienced a problem where
it copied all the cells. I decided at the time that I must be mistaken in my
belief that it only copied the visible cells and ever since I have always
used the Special Cells->Visible method. With this latest episode I think that
the bug was around pre xl2007.

The test worksheet that I created had random data so that the filtered rows
were not consecutive. The following is a copy of a recorded macro which
demonstrates how I created the worksheet.

If I come across the problem again then I will certainly forward it to you.

Sub Macro1()
'Recorded macro to create test data for AutoFilter
Sheets("Sheet1").Select
Range("A1").Select
ActiveCell.FormulaR1C1 = "Col Head 1"
Selection.AutoFill Destination:=Range("A1:F1"), _
Type:=xlFillDefault
Range("A1:F1").Select
Range("A2").Select
ActiveCell.FormulaR1C1 = "=RANDBETWEEN(0,10)"
Selection.AutoFill Destination:=Range("A2:F2"), _
Type:=xlFillDefault
Range("A2:F2").Select
Selection.AutoFill Destination:=Range("A2:F101"), _
Type:=xlFillDefault
Range("A2:F101").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.AutoFilter
Range("A1:F1").Select
Selection.Font.Bold = True
Columns("A:F").Select
Selection.Columns.AutoFit
Range("A2").Select
End Sub

Regards,

OssieMac
Click to expand...
 
Top