macro to copy & fill cells
macro to copy & fill cells
(OP)
Hi Experts.
Columns B and C of the attached file have "sparse data".
Would it be possible to fill - by a VBA macro - all the cells under a certain value with the same value?
For instance:
- B3, B4 -> TOYOTA (as B2)
- C9 - C15 -> MILANO (as C7)
Grateful for any help you can provide
Columns B and C of the attached file have "sparse data".
Would it be possible to fill - by a VBA macro - all the cells under a certain value with the same value?
For instance:
- B3, B4 -> TOYOTA (as B2)
- C9 - C15 -> MILANO (as C7)
Grateful for any help you can provide





RE: macro to copy & fill cells
- select the area for copying (B2:C75)
- F5 / special / blanks / OK
- select B3
- +B2
- CTLR-ENTER
that's all!
now, I'll try to register the code
RE: macro to copy & fill cells
______________________________
Sub copia_sotto()
'
'copia nelle celle vuote sottostanti il valore di una cella
'nb: selezionare in precedenza l'area di applicazione
'
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.FormulaR1C1 = "=+R[-1]C"
End Sub
______________________________
It seems good enough but improvements/suggestions are welcome
RE: macro to copy & fill cells
I guess the only addition I can think of is you might like to select the whole column (from B3) and paste as values.
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
RE: macro to copy & fill cells
Here's my take on the next step. You start by selecting any and all HEADINGS in columns that you want to "fill in the blanks". Then run the procedure. It will put your formula in all blank cells, copies the column of data and pastes values...
CODE
Sub FillInBlanks() 'select the HEADINGS for the columns you want to fill in With Selection With Intersect(.EntireColumn, .CurrentRegion) .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C" .Copy .PasteSpecial xlPasteValues End With End With End SubSkip,
Just traded in my OLD subtlety...
for a NUance!
RE: macro to copy & fill cells
I'll follow your suggestions.
Kind regards,
poli
RE: macro to copy & fill cells
But the code below will do the whole process in one step (assuming that all formulas in the selected range should be converted into text, so use with care):
CODE --> VB
Sub FillInBlanks() 'Select the range you want to fill in Selection.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=+R[-1]C" With Selection .Copy .PasteSpecial xlPasteValues End With End SubDoug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/