Replace blanks in "big" sheet / fastest way

SidKol

New Member
Joined
Oct 14, 2015
Messages
47
Hi all,

With a sheet containing over 100 columns and at about 20.000 rows.
I want to replace all blanks in the usedrange with value "BLANK!"

Manually this is pretty easy + the processing time is acceptable
(find&select - goto special - select "blanks" - enter value and press CNTRL-Enter.)

However I want to include this step into a macro.
I have found multiple ways to do this, but all of them are extremely slow.

Question therefore is how to do this through VBA in the fastest way.


What i found so far
(working for smaller sheets, but too slow for mine)

Code:
Sub ReplaceBlanksSpecialCells()    
    Worksheets("Sheet1").Activate
        ActiveSheet.UsedRange.Select            
        Selection.SpecialCells(xlCellTypeBlanks).Select
        Selection.FormulaR1C1 = "BLANK"
End Sub


Code:
Sub LoopReplaceBlanks()
    For Each c In ActiveSheet.UsedRange
        If c.Value = "" Then c.Value = "-"
        c.HorizontalAlignment = xlCenter
    Next c
End Sub

Hopefully someone knows a faster way.

All help ofcourse greatly appreciated! :)
 
Last edited:

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
maybe if you turn off manual cal and screenupdating the belowwill be faster?

also I changed FormulaR1C1 to value. thought it could make a difference

Code:
Sub ReplaceBlanksSpecialCells()    

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
    Worksheets("Sheet1").Activate
        ActiveSheet.UsedRange.Select            
        Selection.SpecialCells(xlCellTypeBlanks).Select
        Selection.Value = "BLANK"

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub
 
Upvote 0
Are there any formulas? If not I would suggest assigning the value2 property of the usedrange to an array, iterating round the array, then putting in back again once all your replacements are done. Even with calc & screen off, poking values into large numbers of cells is very slow (I assume due to the dependency management etc)
 
Upvote 0
maybe if you turn off manual cal and screenupdating the belowwill be faster?

also I changed FormulaR1C1 to value. thought it could make a difference

Hi Barry, thanks so much for your thoughts.
Tried it, but unfortunately it doesn't seem to make a difference regarding time.
Code still works, but takes at about 15 minutes.
 
Last edited:
Upvote 0
Try:
Code:
Sub FillBlanks

Dim arr() as Variant
Dim rng as Range
Dim x  as Long
Dim y as Long
Const myValue as String = "BLANK"

With Application
  .ScreenUpdating = False
  .Calculation = xlCalculationManual
End With

With Sheets("Sheet1")
  Set Rng = .Cells.SpecialCells(xlCellTypeLastCell)
  arr = .Range(.cells(1,1), rng).Value2

  For x = lBound(arr,1) to Ubound(arr,1)
    For y = lbound(arr, 2) to Ubound(arr, 2)
        If Lenb(arr(x,y)) = 0 Then arr(X,y) = myValue
    Next y
  Next x

  .cells(1,1).resize(Ubound(arr, 1), ubound(arr, 2)).Value = arr

End With

Erase arr
Set rng = Nothing

With Application
  .ScreenUpdating = True
  .Calculation = xlCalculationAutomatic
End With

End Sub
 
Last edited:
Upvote 0
Are there any formulas? If not I would suggest assigning the value2 property of the usedrange to an array, iterating round the array, then putting in back again once all your replacements are done. Even with calc & screen off, poking values into large numbers of cells is very slow (I assume due to the dependency management etc)

Thank you so much.
There are no formulas related to any data in this worksheet. (It's clean :))
My VBA knowledge is still limited. I do not know how to test your suggestions / how to turn them into code.
 
Last edited:
Upvote 0
Thank you JackDanIce.

This looks quite impressive.
At first i got "Compile error: Syntax error" on
Code:
If IsEmpty arr(x,y) Then arr(X,y) = myValue
Simple googling learned that a bracket seems to be missing - so looks like this should be
Code:
If IsEmpty(arr(x, y)) Then arr(x, y) = myValue

Anyway with this - it's working like a charm!!

Now I am extremely curious ; as the code is way above what I can do with VBA yet.
Do you mind explaining in (easy) language what this code does? What the steps are, so that I can understand why this actually works so much faster?
 
Upvote 0
You're welcome. I actually made a tiny change to the IsEmpty part, however, I'll revert back to that as it's working for you.

The code:

- Loads the worksheet into an array kept in the PC's memory. You can process data faster when it's in memory than when the code has to interact with the worksheet
- Uses a nested loop to test every array cell if it's empty, if it is, fill with a value
- Print the entire array (with the blank cells filled) to the worksheet

Printing the entire array in a single action is significantly faster than updating each cell individually

Code with comments below:
Code:
Sub FillBlanks()
    
    'Variables used in code to store/process data
    '--------------------------------------------
    Dim arr() As Variant                'An array which we'll use to fill with the contents of the worksheet
    Dim rng As Range                    'A Range variable used to identify the last used cell of the worksheet
    Dim x  As Long                      'A numerical variable
    Dim y As Long                       'A numerical variable
    Const myValue As String = "BLANK"   'The constant variable to fill blank cells with
    
    
    With Application
      .ScreenUpdating = False            'Turn off screenupdating to speed up exectuion
      .Calculation = xlCalculationManual 'Turn off formula calculation to speed up execution
    End With
    
    With Sheets("Sheet1")
      Set rng = .Cells.SpecialCells(xlCellTypeLastCell) 'Set rng to be the last used cell on the worksheet
      'We define the worksheet area to put into the array as having top-left corner A1 and bottom-right corner rng
      arr = .Range(.Cells(1, 1), rng).Value2
      
      'Nested loop to loop through each row and column of the array, mimicking the worksheet itself
      For x = LBound(arr, 1) To UBound(arr, 1)
        For y = LBound(arr, 2) To UBound(arr, 2)
            'If an array cell is empty, we replace it the constant myValue
            If IsEmpty(arr(x, y)) Then arr(x, y) = myValue
        Next y
      Next x
    
      '"Print" the array contents back to the worksheet, the area printed to must match the array size
      'It takes longer to manually update each and every worksheet cell
      .Cells(1, 1).Resize(UBound(arr, 1), UBound(arr, 2)).value = arr
    
    End With
    
    Erase arr         'Clear the array variable
    Set rng = Nothing 'Clear the rng object
    
    With Application
      .ScreenUpdating = True                'Turn screenupdating back on
      .Calculation = xlCalculationAutomatic 'Turn calculation mode to Automatic
    End With

End Sub
 
Upvote 0

Forum statistics

Threads
1,223,275
Messages
6,171,119
Members
452,381
Latest member
Nova88

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top