Copying And Pasting Values Across Multiple Rows In A Filtered List In Microsoft Excel
That sounds like a complicated task in itself, without even considering how unhelpful MS Excel can be…. What exactly am I trying to achieve?! How frequently does this need occur?
I work with massive spreadhseets. Sure I should probably use a specific database application, but seeing as Access sucks balls and everybody in my company literally wants to marry Excel I have little choice in the matter… I run a couple of databases, the specifics of which are unimportant, which have around 30-40 thousand rows acting as records. I have the data set up with auto filters for the coloumn headers so that I can show data that matches specific criteria in each coloumn (show all rows where postcode starts “AB” for example) or order rows according to any specific coloumn (like sort rows in order of postcode).
The problem occurs when working on specific subsets of this data. Fow example, say I am working on all accounts within the AB postcodes, where there is no manager name. I can add a formula into the top cell, a vlookup for example, and autofill this formula to the bottom of the displayed rows. Excel intelligently only applies this formula to the visible cells which is great… the hidden rows between which have been excluded by the filter are unaffected, which makes sense.
Now, with 40,000 rows and 15 coloumns, my spreadsheet has 600,000 cells. Thats over half a million pieces of data to hold in relation to each other. Obviously Excel begins to labour - changing filter criteria can take a couple of seconds, even using the end key to navigate to the bottom of the data can take a couple of seconds where its normally instant.
This in itself is not an issue but the problem is compounded with formulas. Every time you change a filter, Excel works out which rows to show and then recalculates every formula in the sheet. The more formulae you have the longer it takes until eventually you’re waiting hours to show just one postcode.
Most people profficient or at least familiar with Excel formulas know copying and pasting the cells that contain formulas as values (eg Copy, Paste Special, Paste Values) cuts out this added complication.
But that isn’t that easy when working with filtered lists! Try and copy a selection of cells in a filtered list and then paste them back on top as values and you’ll more than likely get some errors. The problem stems from hidden rows between the rows you are copying/pasting. Excel fails miserably to ignore these hidden cells, and if it works at all it will almost certainly overwrite hidden data with the copied values, leave formulas in other rows and generally fuck about with your karma.
The obvious work around is to release all criteria on the filters (Data -> Filter -> Show All is the easiest method) and copy any coloumn affected by your newly added formulas and then immediately paste them as values. This effectively replaces all formulas in these ranges with their “answers” and leaves any formatting intact.
The problem is, when I’m repeating this process maybe 10 - 20 times a day it gets intensely frustrating. Once you’ve done this, you still need to reapply the criteria you had and in a big spreadsheet this whole workaround can take a couple of minutes as Auto Filter needs to recalculate its visible data and all the formulas you haven’t changed yet. These minutes soon add up!
So I’ve hit another solution - a very simple macro which I’ve currently got assigned to a toolbar button which gets more use than any other.
Public Sub Values()
Dim cell As ObjectSelection.SpecialCells(xlCellTypeVisible).Select
For Each cell In Selection
cell.Value = cell.Value
Next cellEnd Sub
Once I’ve copied the formula down (or across etc) I select the cells that contain formula in the usual way and then run the macro.
It first takes the selection and very quickly narrows the selection to only visible cells. This is the most important part because as discussed, any initial selection you make contains the hidden cells between the selected visible cells.
Lastly it cycles through each cell in this new narrowed selection and copies the value directly into the cell. So if the cell contained the formula =2+2, the macro will take the value 4 and reset the cell to that value. This works for any formula, however complex.
The result is the ability to copy formulas and paste their values into any selection of cells, whether the are filtered with Auto Filter or not. Its how Copy and Paste Values should work - in fact its how copy and paste should behave full stop, but thats a different less elegent problem.
Reading back through it all seems a bit over the top, but so far it saves me about half an hour a day which is about 120 hours a year (taking into account holidays!) - thats 3 and a half weeks of my working life I got back! If only I hadn’t just run out of things to do!!
Filed by Kieran at April 30th, 2008 under Code, Excel, Geek!