Fill Blank Cells with Formula From Row Above

[millerprm]

Where I work, Excel 2007 is still being used. When doing autofill on workbooks with thousands of rows, you get an error stating "Selection is too large" because of the 8192 different areas in the special cells range issue. I found code online (by Dave Peterson by way of Ron De Bruin) that will work around this using a Do While loop on 8000 rows at a time, but it fills the rows with values. I need something that fills each blank with the FORMULA from the cell above. Any examples would be greatly appreciated. Code is below. Much Thanks

Dim wks As Worksheet
Dim rng As Range
Dim rng2 As Range
Dim LastRow As Long
Dim col As Long
Dim lRows As Long
Dim lLimit As Long

Dim lCount As Long
On Error Resume Next
 
lRows = 2 'starting row
lLimit = 8000

Set wks = ActiveSheet
With wks
   col = ActiveCell.Column

   Set rng = .UsedRange  'try to reset the lastcell
   LastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row
   Set rng = Nothing
   
    lCount = .Columns(col).SpecialCells(xlCellTypeBlanks).Areas(1).Cells.Count
    
    If lCount = 0 Then
        MsgBox "No blanks found in selected column"
        Exit Sub
    ElseIf lCount = .Columns(col).Cells.Count Then
        MsgBox "Over the Special Cells Limit" 'this line can be deleted
        Do While lRows < LastRow
            Set rng = .Range(.Cells(lRows, col), .Cells(lRows + lLimit, col)) _
                           .Cells.SpecialCells(xlCellTypeBlanks)
            rng.FormulaR1C1 = "=R[-1]C"
            lRows = lRows + lLimit
        Loop
    Else
        Set rng = .Range(.Cells(2, col), .Cells(LastRow, col)) _
                       .Cells.SpecialCells(xlCellTypeBlanks)
        rng.FormulaR1C1 = "=R[-1]C"
    End If

   'replace formulas with values
   With .Cells(1, col).EntireColumn
       .Value = .Value
   End With

End With

 

[Michael M]

might be missing something, but can't you simply remove these lines

   'replace formulas with values
   With .Cells(1, col).EntireColumn
       .Value = .Value
   End With

 

[millerprm]

might be missing something, but can't you simply remove these lines

   'replace formulas with values
   With .Cells(1, col).EntireColumn
       .Value = .Value
   End With

thanks for the quick reply. i did comment out the code, but when i ran the macro again, the column was still filled with values

 

[Michael M]

Are there formulas in the line above the blanks ??
The code works fine for me !!
But you simply appear to be copying the formula down the page...if that's so, why not use this instead
It worked for 20000 rows

Sub MM1()
Dim lr As Long
col = ActiveCell.Column
lr = Cells(Rows.Count, col).End(xlUp).Row
Range(Cells(2, col), Cells(lr, col)).Formula = "your starting line formula goes here"
End Sub

 

[millerprm]

Are there formulas in the line above the blanks ??
The code works fine for me !!
But you simply appear to be copying the formula down the page...if that's so, why not use this instead
It worked for 20000 rows

Sub MM1()
Dim lr As Long
col = ActiveCell.Column
lr = Cells(Rows.Count, col).End(xlUp).Row
Range(Cells(2, col), Cells(lr, col)).Formula = "your starting line formula goes here"
End Sub

thanks again for the quick reply. yes, row 2 has a formula in every column, and there are several. but i'll give your idea a try on one of the columns. thanks!