Fill Blank Cells with Formula From Row Above

millerprm

New Member
Joined
Jan 14, 2015
Messages
6
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

Code:
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
 
Last edited by a moderator:

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
18,138
Office Version
2013
Platform
Windows
might be missing something, but can't you simply remove these lines
Code:
   'replace formulas with values
   With .Cells(1, col).EntireColumn
       .Value = .Value
   End With
 

millerprm

New Member
Joined
Jan 14, 2015
Messages
6
might be missing something, but can't you simply remove these lines
Code:
   '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

Well-known Member
Joined
Oct 27, 2005
Messages
18,138
Office Version
2013
Platform
Windows
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
Rich (BB code):
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

New Member
Joined
Jan 14, 2015
Messages
6
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
Rich (BB code):
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!
 

Forum statistics

Threads
1,081,574
Messages
5,359,704
Members
400,545
Latest member
Damntheman30

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top