VB code to fill in blank cells with formula

[pagrender]

Hi,

My original intention with this macro was to "fill in" empty cells with a formula referencing the cell directly above it in the given range. The macro ended up replacing the data that was in all the cells (not just the blank cells) with the formula. Basically, I now have 400,000 rows of the same data.

This macro works with a smaller range, such as A2:G30. Is this not working because my range has so much data in it? Is there a better way to write this macro so it works for me?

Thanks,
Pete

Sub test()
'
Range("A2:G395992").Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.FormulaR1C1 = "=R[-1]C"
Range("A1").Select
MsgBox ("Done!")
End Sub

 

[VoG]

Interesting

Does it work without selecting?

Sub test()
'
Range("A2:G395992").SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
MsgBox ("Done!")
End Sub

 

[pagrender]

Hi Vog!

Thanks for the quick reply. The results were the same as I have 400,000 of only formulas with no constants anymore.

Can it be written to do this one row at a time? I know it will take a great deal of time to run the macro, but would that work? If so, can you write that for me?

Thanks,
Pete

 

[VoG]

Hi. I think that you have exceeded some magic number (8192?) but try this (just before you go to bed because it will take ages)

Sub Forever()
Dim i As Long, j As Long
For i = 2 To 395222
    For j = 1 To 7
        If Cells(i, j).Value = "" Then Cells(i, j).Value = Cells(i - 1, j).Value
    Next j
Next i
End Sub

 

[pagrender]

I will heed your advice and try this later. I'll let you know how it comes out. Thanks for your help!

 

[pagrender]

It only took 13 minutes... and it looks like it worked!

Thanks again!

 

[Jonmo1]

If it is a limit of 8192 for specialcells then try this.
Might be a quicker loop

For i = 2 to 395992 Step 8000
    Range("A" & i).Resize(8000,1).SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
Next i

 

[pagrender]

That's an interesting solution JonMo. If I'm reading that correctly it looks like it repeats the process in blocks of 8000 so that probably would speed it up. Thanks for the suggestion.

 

[Jonmo1]

Yep, that's exactly what it's intended to do.

 

[pagrender]

I just ran it on another copy of my file (since I used Vog's solution already)... it only took about a minute. Which is pretty fast to look at 2.7 million excel cells!