To fill blank cells, or delete rows with blanks cells, you can use Excel’s Go To Special feature.
For example, in the worksheet shown below, you might want to fill in all the blanks in column B, by copying the value from the row above.
There are instructions on the Contextures website to fill blank cells, by using Go To Special to select the blanks. You can do this manually, and there’s sample code to make the job easier.
Selection Is Too Large Error
This technique works very well, unless you’re trying to fill blank cells in a long list. In that case, you might see the error message, “Selection is too large.”
This happens in Excel 2007, and earlier versions, because there is a limit of 8192 separate areas that the special cells feature can handle. (This problem has been fixed in Excel 2010.) There are details on Ron de Bruin’s website: SpecialCells Limit Problem.
Work in Smaller Chunks
If you run into this error, you can work with smaller chunks of data instead.
- If you’re making the changes manually, select a few thousand rows, instead of the full column.
- If you’re using a macro, you can loop through the cells in large chunks, e.g. 8000 rows, instead of trying to change the entire column.
On the Contextures website, Fill Blank Cells Macro – Example 3 checks for the number of areas, using Ron’s sample code, and uses a loop if necessary. The code is shown below, and it shows a message box if the range is over the special cells limit. You can remove that line — it’s just there for information.
Sub FillColBlanks() 'http://www.contextures.com/xlDataEntry02.html 'by Dave Peterson 2004-01-06 'fill blank cells in column with value above '2010-10-12 incorporated Ron de Bruin's test for special cells limit 'http://www.rondebruin.nl/specialcells.htm 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 End Sub