PDA

View Full Version : Solved: in Excel 2007, pasting to visible cells only



K. Georgiadis
03-14-2009, 09:08 AM
I have an 8700-row static data table with 9 columns. I want to filter the data by product and state and create new dynamic formulas for each/product combination and then copy them down to ONLY the visible rows that have met the same filter criteria.

I checked with the Microsoft Communities Group and was told that Excel does not have a built-in function to paste formulas to visible cells only but that this can be accomplished with VBA code. Could you help me with this?

Thanks in advance

mdmackillop
03-14-2009, 09:16 AM
Can you make up and post a sample workbook showing exactly what you are after?

K. Georgiadis
03-14-2009, 09:34 AM
I'll see what I can do. The data table has 8700+ rows and it will take a little thinking to come up with a representative fragment.

I can already tell you that the hidden rows do NOT occur at regular intervals.

PS: OK, here is a representative fragment of the file. Right now the table is filtered for corn. Columns D:J contain static numbers which need to be replaced with dynamic formulas. I want to create formulas in D2:J2 and copy them down to the visible rows only.

Obviously, a manual paste is easy enough in this sample but the real table has 8760 rows.

K. Georgiadis
03-14-2009, 10:20 AM
Can you make up and post a sample workbook showing exactly what you are after?

My original reply above has been edited and now contains the sample file that you requested.

Thanks!

mdmackillop
03-14-2009, 10:29 AM
What formula would you like in D2 for exampleo?

mdmackillop
03-14-2009, 10:40 AM
Probably something like this
Sub Copies()
Dim Rng As Range
Dim Fmla As Range
Set Fmla = Range("D2:J2")
Set Rng = Range(Range("D2").Offset(1), Range("D2").Offset(1).End(xlDown)).Resize(, 7)
Fmla.Copy
Rng.SpecialCells(xlCellTypeVisible).PasteSpecial (xlPasteFormulas)
End Sub

K. Georgiadis
03-14-2009, 10:42 AM
The fragment that I gave you is over-simplified and the actual worksheet is a lot more complex.

Each State/product combination has its own formula, linking to other sections of the workbook with named ranges and INDEX functions. I would enter the formula in cell D2 and then copy it across. Then I would want to paste the formulas of D2:J2 to all visible rows that have met the filtering criteria.

In a real situation, and in the absence of a "smart" solution, I would need to paste the row manually 100-300 times.

If, in light of this clarification, you still suggest the above code, I will certainly give it a try.

mdmackillop
03-14-2009, 10:46 AM
Sample containing test formulae

K. Georgiadis
03-14-2009, 11:29 AM
Sample containing test formulae

I tested it and it seems to be working fine. What can be added to the code so that it also copies the fill color of D2:J2?

(I shade the starting row light yellow so that I have a visual check of the rows that have been modified)

Many thanks!

mdmackillop
03-14-2009, 11:37 AM
You may need to set the colorindex value for your own PC
Sub Copies()
Dim Rng As Range
Dim Fmla As Range
Set Fmla = Range("D2:J2")
Set Rng = Range(Range("D2").Offset(1), Range("D2").Offset(1).End(xlDown)).Resize(, 7)
Fmla.Copy
With Rng.SpecialCells(xlCellTypeVisible)
.PasteSpecial (xlPasteFormulas)
.Interior.ColorIndex = 36
.Cells(1).Select
End With
Application.CutCopyMode = False
End Sub

K. Georgiadis
03-14-2009, 11:48 AM
Fantastic! You are going to save me tons of time. Many thanks:beerchug:
Marking as solved.

wpilgri
10-31-2011, 08:15 AM
Here's a related question. Using the above sample file, what if I wanted to filter the list (Corn for Grain as an example) and copy all the visible values from the 2009 column to the 2008 column. I can't seem to get any combination of pastespecial to work for me. Any ideas? Thank You

mdmackillop
11-06-2011, 11:23 AM
Can you post sample data? It seems to have disappeared.