Replace blanks in "big" sheet / fastest way

[SidKol]

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)

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

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!

 

[BarryL]

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

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

 

[macleanb]

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)

 

[SidKol]

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.

 

[JackDanIce]

Try:

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

 

[SidKol]

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.

 

[SidKol]

Thank you JackDanIce.

This looks quite impressive.
At first i got "Compile error: Syntax error" on

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

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?

 

[JackDanIce]

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:

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