Good day all,

Normally, I'd just continue using the 'fill handle' to complete my
work, but I'm working with a xls with over 10,000 rows and I'm sure
there is a better way.

I have pasted some code below that seems to work, but I need to have it
modified a bit to meet my needs. I'd like it to ask the user which
column to search, and work from the top down instead of the bottom up.
What I need to do is take the first cell value in the specified
column, and copy it the all the blank cells below it - until it runs
into a new value - then take that cell value, and copy that to all the
blank cells below it and so on. I guess the next question would be,
'how do stop the routine from ending ie copying 66000 rows" Well,
there will always be a value to the right of the searched column, so I
guess if there is no value, the routine could stop.

Thank you in advance...Here the code I found:

Sub Tester6()
Dim rng2 As Range, rng As Range
Set rng2 = Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))
On Error Resume Next
Set rng = rng2.SpecialCells(xlBlanks)
On Error GoTo 0
If Not rng Is Nothing Then
rng.FormulaR1C1 = "=R[-1]C"
rng2.Formula = rng2.Value
Else
MsgBox "Non found"
End If
End Sub

Hi Craig,

Here is what I use for that problem - it is a bit different.

Assuming column A contains your data with empty bits and column B is your
full column, why don't you just

Add a column on the right with this formula in cell C3:
=IF(A3="",C2,A3)
then just autofill that one down. It should go all the way down.
It will calculate for a while but then so will your macro.

Then you can copy-paste values in that column and paste it over column A.

--
Reply by thread only
thanks
Allllen


"Craig Freeman" wrote:

Good day all,

Normally, I'd just continue using the 'fill handle' to complete my
work, but I'm working with a xls with over 10,000 rows and I'm sure
there is a better way.

I have pasted some code below that seems to work, but I need to have it
modified a bit to meet my needs. I'd like it to ask the user which
column to search, and work from the top down instead of the bottom up.
What I need to do is take the first cell value in the specified
column, and copy it the all the blank cells below it - until it runs
into a new value - then take that cell value, and copy that to all the
blank cells below it and so on. I guess the next question would be,
'how do stop the routine from ending ie copying 66000 rows" Well,
there will always be a value to the right of the searched column, so I
guess if there is no value, the routine could stop.

Thank you in advance...Here the code I found:

Sub Tester6()
Dim rng2 As Range, rng As Range
Set rng2 = Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))
On Error Resume Next
Set rng = rng2.SpecialCells(xlBlanks)
On Error GoTo 0
If Not rng Is Nothing Then
rng.FormulaR1C1 = "=R[-1]C"
rng2.Formula = rng2.Value
Else
MsgBox "Non found"
End If
End Sub

Thank you Allllen,

Very clever. Simple and effective, I like it, and I'll definitely use
it.

Although, I'm still a little curious to see if there is anyone who is
able to modify the code I posted to work as well as your solution.
Anyone?

Craig