paste over a filtered range

[Guest]

I have a spreadsheet with 40000 records, I have filtered down to 1000 records
that have one coloumn of incorrect information that needs replacing, how do I
paste the new data over the old?? What keeps happening is the new data
pastes over the first 1000 records, not only the filtered recorsds

 

[Gord Dibben]

freddie

When you have your filter in place and the 1000 records are showing, hit
F5>Special>Visible cell only and OK.

Now paste your replacement data.


Gord Dibben Excel MVP

 

[Dave Peterson]

That caused a problem when I did this (xl2003).

I put some test data in A1:I25
I filtered to see only row 3 and row 7.

Then I put some more test data in A30:I31 and copied it.

I selected Rows 4:7 and did the edit|goto special|visible cells only.

I pasted into that selection.

When I showed all my data, I saw my paste had affected both rows 3 and 4 and
rows 7 and 8.

 

[Dave Peterson]

Can you sort that range to put all the data together, then paste into that
contiguous area?

I like to put 1, 2, 3, ... down a helper column.

Then I can sort by whatever I want. Do what I need to do and then sort by that
helper column to put things back in the original order.

 

[Debra Dalgleish]

When you paste, it will paste into a contiguous range, not the visible
cells in the filtered range.

Perhaps you could mark the visible rows, by putting an X in an adjacent
column. Then, sort by the marked column, and paste into the grouped cells.

 

[Guest]

Thanks for the quick response, unfortunatley it didn't work.... I get the
error message "The information cannot be pasted because the Copy area and the
Paste area are not the same size and shape..."

Any suggestions??

 

[Ken Wright]

If (and only if) by any chance the data you are replacing is the same value
for *all* your filtered rows, then do Edit / Go To Special / Visible Cells
only, enter the replacement data in the first selected cell and then use
CTRL+ENTER to enter the data, at which point it will be propogated to all
the visible cell selected.

 

[Gord Dibben]

Dave and Debra are correct.

My method doesn't work on non-contiguous cells.

Back to tester's school<g>


Gord

 

[JulieD]

Hi Freddie

not sure how to achive this, but are you pasting the SAME VALUE over all the
1000 records (ie updating "Dept" to "Department"), if so you can use the
visible cells only option with control & enter

.... try on a copy of your workbook

filter the data and select the column you want to replace, choose edit /
goto / special - visible cells, ok
now without clicking anywhere type the new value and press CONTROL & ENTER