Macro for filling in empty cells in selection

[Floris Blaauboer]

Hi,

I am trying to create a Macro which will look at all cells in a
selection, and if the cell is empty takes the value from the top and
puts it in this cell (so it should run top-down in a selection).

Can anyone help me on this one?

Regards,

Floris

 

[ankur]

Hi Floris,

Try this...

Sub Replaceblanks()
Dim rng as Range, rng1 as Range
set rng = Range(cells(1,1),cells(rows.count,2).End(xlup).offset(0,-1))
set rng1 = rng.specialcells(xlBlanks)
rng1.formulaR1C1 = "=R[-1]C"
rng.Formula = rng.Value
End sub

Ankur Kanchan
www.xlmacros.com

 

[Floris Blaauboer]

Perfect, thank you.

Now is there an easy way to make sure this macro only runs for the
selected cells instead of the entire worksheet?

Floris

Hi Floris,

Try this...

Sub Replaceblanks()
Dim rng as Range, rng1 as Range
set rng = Range(cells(1,1),cells(rows.count,2).End(xlup).offset(0,-1))
set rng1 = rng.specialcells(xlBlanks)
rng1.formulaR1C1 = "=R[-1]C"
rng.Formula = rng.Value
End sub

Ankur Kanchan
www.xlmacros.com

Hi,

I am trying to create a Macro which will look at all cells in a
selection, and if the cell is empty takes the value from the top and
puts it in this cell (so it should run top-down in a selection).

Can anyone help me on this one?

Regards,

Floris

 

[Don Guillett]

Sub replaceblanks()
For Each c In Selection.SpecialCells(xlBlanks)
'MsgBox c.Address
c.Value = c.Offset(-1)
Next c
End Sub

 

[Gord Dibben]

Floris

Sub Replaceblanks()
Dim rng As Range, rng1 As Range
Set rng = Selection
Set rng1 = rng.SpecialCells(xlBlanks)
rng1.FormulaR1C1 = "=R[-1]C"
rng.Formula = rng.Value
End Sub


Gord Dibben MS Excel MVP

Perfect, thank you.

Now is there an easy way to make sure this macro only runs for the
selected cells instead of the entire worksheet?

Floris

Hi Floris,

Try this...

Sub Replaceblanks()
Dim rng as Range, rng1 as Range
set rng = Range(cells(1,1),cells(rows.count,2).End(xlup).offset(0,-1))
set rng1 = rng.specialcells(xlBlanks)
rng1.formulaR1C1 = "=R[-1]C"
rng.Formula = rng.Value
End sub

Ankur Kanchan
www.xlmacros.com

Hi,

I am trying to create a Macro which will look at all cells in a
selection, and if the cell is empty takes the value from the top and
puts it in this cell (so it should run top-down in a selection).

Can anyone help me on this one?

Regards,

Floris

 

[Floris Blaauboer]

I would like to thank everybody for all the help.

In the end the solution as provided by Don worked ideally, although the
second one was also fine if one would simply pick the correct
selection.

Floris

 

[Don Guillett]

Glad to help. You could specify the selection, such as
lr=cells(rows.count,"a").end(xlup).row
lc=cells(1,columns.count).end(xltoleft).column

for each c range(cells(1,1),cells(lr,lc))