Macro to autofill (copy paste)

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