macro to copy & fill cells 3

[poli60]

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

 

[poli60]

first attempt:
- 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

 

[poli60]

VBA code
______________________________
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

 

[IDS]

That's pretty neat.

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/

 

[SkipVought]

I like your solution! Have a StAr!

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...

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 Sub

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[poli60]

Thank you Doug and Skip.
I'll follow your suggestions.
Kind regards,
poli

 

[IDS]

Skip's code didn't work for me, probably because I didn't read the comment, and selected the whole range rather than the headings.

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):

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 Sub

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/