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)
Hopefully someone knows a faster way.
All help ofcourse greatly appreciated!
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: