Fill in Blanks

[Steve Stad]

I would like to fill in blank cells in one Column with the value of the
nonblank cell above the blank cells. e.g., automatically fillin A2 and A3
with 'orange' and fill in A5, and A6 with 'apple'. Can you suggest macro
steps?
A
1 orange
2
3
4 apple
5
6
7

 

[ker_01]

Macro solution:

'A is a column that will always have data in your data set,
'B is the column that has blanks to be filled
Sub fill_blanks()
For i = 1 to 10000 '<-increase to be larger than your max number of rows
if Sheet1.range("A" & i).value="" then exit sub
If Sheet1.range("B" & i).value <>"" then
tempval = Sheet1.range("B" & i).value
else
Sheet1.range("B" & i).value = tempval
endif
Next
end sub

However, and easier solution: insert a new column, and starting in row 2 (B2
in your example)
=if(A2<>"",A2,,A1)

copy it all the way down, then copy/paste special/values over the original
column... then delete the (temporary) column that has this formula in it.

HTH,
Keith

 

[Rick Rothstein]

Give this macro a try...

Sub FillInTheBlanks()
Dim Area As Range, LastRow As Long
Const ColLetter As String = "A"
On Error Resume Next
LastRow = Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlFormulas).Row
For Each Area In Columns(ColLetter)(1).Resize(LastRow). _
SpecialCells(xlCellTypeBlanks).Areas
Area.Value = Area(1).Offset(-1).Value
Next
End Sub

 

[Ron de Bruin]

with and Without a macro examples

http://www.contextures.com/xlDataEntry02.html

 

[Steve Stad]

keith - the easy solution does not work b/c when you copy
'=if(A2<>"",A2,,A1)' down it copies the blank, i.e., a2 and a3, not the
nonblank cell 'a1' above the blank cells.

 

[Steve Stad]

Thanks Ron - The video at the web site really helped.

 

[ker_01]

Apologies for the typo, it looks like there is an extra comma in there- and
the value it should copy is the cell above, not the one in A; should be
(B1) =A1
(B2) =if(A2<>"",A2,B1)
then copy the B2 formula down.

This also assumes that A2 (and below) is truly blank; if you have a space,
formula, or other characters, then this formula would have to be updated
accordingly... just giving you some ideas on alternate ways to get a working
solution.

HTH,
Keith