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

 

[Jonmo1]

Great,

Might be even faster with this added..

Dim PrevCalc As Variant
With Application
    .EnableEvents = False
    .ScreenUpdating = False
    PrevCalc = .Calculation
    .Calculation = xlCalculationManual
End With
 
For i = 2 to 395992 Step 8000
    Range("A" & i).Resize(8000,1).SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
Next i
 
With Application
    .EnableEvents = True
    .ScreenUpdating = True
    .Calculation = PrevCalc
End With

 

[p45cal]

jonmo1, if the limit is 8192, and that's the number of areas that Excel can handle (limit removed i n Excel2010 btw), then the largest number of areas that can be crammed into a single column as in your example is where you have alternate single cells selected. This means your step size could be doubled.
However, there are 7 columns in the range here and the max number of areas is obtained with alternate cells in each row, but staggered from one row to the next, that is, in this case, a max of seven areas for each pair of rows, so it would be safer to use steps of say 2000 (more precisely 2340) at the same time adjusting
Resize(8000,1)
to
Resize(2000,7).
I'd be interested to know how much quicker this is over other methods; I'd expect it to be very much faster since it only requires some 200 iterations.

 

[pagrender]

Hi again,

Tell you what... I'll run the macro with all the methods provided sometime over the next day or two.

Is there a way to build a message box to pop up at the end of the macro to tell me how much time was elapsed? That would make it easier for me.

By the way, I had to modify JonMo's code to say resize(8000,7) from resize (8000,1). When I ran it with 8000,1 it only worked in column A. It did work with Excel 2007 when I used 8000,7. Technically, wouldn't that be 56,000 cells? Does the number of cells that have information in them get subtracted from that count (as in, if there were 55,000 cells that had data does that mean we are only looking at 56,000-55,000=1,000 cells, which is within the 8,192 limit?)

Thanks again everyone,
Pete

 

[p45cal]

It did work with Excel 2007 when I used 8000,7. Technically, wouldn't that be 56,000 cells? Does the number of cells that have information in them get subtracted from that count (as in, if there were 55,000 cells that had data does that mean we are only looking at 56,000-55,000=1,000 cells, which is within the 8,192 limit?)

Yes, but to be properly robust, you should code in such a way that the limit can never be reached, and since it's the areas count that matters (rather than the cells count) after specialcells has been applied then you'd be safer with step sizes and resizes of 2000 rows. The danger associated with not doing this is that excel proir to excel 2010 will not raise an error and will just go ahead and add formulae to the whole range! see
http://support.microsoft.com/default.aspx?scid=kb;en-us;832293
(where it refers to a cells limit of 8192 in thes article, investigations lead me to think this should read read areas)

 

[pagrender]

Thanks for clearing that up. That makes sense now (not that anything VB related ever made much sense to me!). I appreciate your help.