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
"Ron de Bruin" wrote:
> 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.
>
> --
>
> Regards Ron de Bruin
> http://www.rondebruin.nl/tips.htm
>
>
> "OssieMac" <(E-Mail Removed)> wrote in message news:322A7EBB-3C08-4FB1-99F9-(E-Mail Removed)...
> > 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
> >
> > "Dave Peterson" wrote:
> >
> >> 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.
> >>
> >>
> >>
> >> OssieMac wrote:
> >> >
> >> > 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
> >> >
> >> > "Harlan Grove" wrote:
> >> >
> >> > > "gary" <(E-Mail Removed)> wrote...
> >> > > >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?
> >> > >
> >> > > 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.
> >> > >
> >> > >
> >> > >
> >>
> >> --
> >>
> >> Dave Peterson
> >>
>
Similar Threads
