Macro for filling in empty cells in selection

F

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
 
A

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
 
F

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

Floris said:
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
Click to expand...
Click to expand...
 
D

Don Guillett

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

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

Floris said:
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
Click to expand...
Click to expand...
Click to expand...
 
F

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
 
D

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