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
Last edited by K. Georgiadis; 03-14-2009 at 09:32 AM.
Can you make up and post a sample workbook showing exactly what you are after?
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
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.
Last edited by K. Georgiadis; 03-14-2009 at 10:18 AM.
My original reply above has been edited and now contains the sample file that you requested.Originally Posted by mdmackillop
Thanks!
What formula would you like in D2 for exampleo?
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Probably something like this
[VBA]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
[/VBA]
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
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.
Sample containing test formulae
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
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!
You may need to set the colorindex value for your own PC
[VBA]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
[/VBA]
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Fantastic! You are going to save me tons of time. Many thanks
Marking as solved.
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
Can you post sample data? It seems to have disappeared.
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Posting Permissions
Reply With Quote
