Copy value and fill blank spaces below it until new value is encountered?

[drod]

Hi,

I have a column of data as follows in excel, with a value, followed by
multiple blank cells, then a new value, and so forth:

_Column_A:_

1GT34Z
<blank>
<blank>
2FR56T
<blank>
<blank>
<blank>
5VG56R
<blank>
etc....

How do I create a macro that begins at the top of the column A, and
then proceeds vertically down cell by cell, checking whether a value is
present? If there is a value present, it will move on, however, if
there is NOT a value present, it will check the cell directly above it
and copy that value into it, as well as every consecutive blank row
following it, until a cell with a different value is encountered.
Hence, when the macro is complete, all the rows that were originally
<blank> will have a value present in it based on whatever value it
followed. Then proceed to column B and repeat.

Thanks in advance!
drod

 

[mudraker]

Is there a limit to how many rows it will copy down or do you expect the
macro to fill down to the last row in excel 65536 rows

 

[drod]

I only need it to go down to the end of the values of the data, not the
entire end of the spreadsheet. I guess in this instance, I could enter
the value "end" after the final value in the column and then ask the
macro to stop and move to the next row when it encounters "end".

Or is there a more effecient way?
Thanks.

 

[Gord Dibben]

drod

Sub Fill_Blanks()
Dim myRange As Range
Set myRange = Selection
On Error GoTo stopnow
If myRange.Cells.Count = 1 Then
MsgBox "Select a range first."
Else
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.FormulaR1C1 = "=R[-1]C"
End If
stopnow:
End Sub

You can select multiple columns before running.

Gord Dibben Excel MVP

 

[drod]

Gord, thanks for the help, its working great!

dro