Copy cell value to empty cells

[paulette33]

Hi -

I have a column with a series of values and empty cells in between. When I find a cell with value and empty cells following, I want to copy that value into the empty cells. I need to do this for the entire column. What is the easiest way to do this without have to manually autofill or copy and paste.

I've gotten a Do Loop to cycle through the cells looking for the empty cell and can copy that until it reaches the next cell with value. But, how do I get it to continue until it has gone through the entire column?

Hope this makes sense and thanks in advance!

 

[PHV]

A starting point (for column A):
Sub myAutoFill()
Dim i As Long, x
With ActiveSheet
x = .Cells(1, 1)
For i = 2 To .UsedRange.SpecialCells(xlCellTypeLastCell).Row
If Trim(.Cells(i, 1) & "") = "" Then
.Cells(i, 1) = x
Else
x = .Cells(i, 1)
End If
Next
End With
End Sub

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[firefytr]

If I'm understanding you right, there should be no need for VBA at all...

Select the entire column (or range desired)
Press F5 | Special | Blank cells
Hit the = sign
Press the Up Arrow
Hit Ctrl + Enter

To leave them as values you must select the entire column, press Ctrl + C, then Alt + E, S, V. If you have other formulas in the column then you probably don't want to do that, but you can't copy/paste values to a non-contiguous range.

HTH

-----------
Regards,
Zack Barresse

 

[paulette33]

Thank you both for your help!!!!