Auto fill data range

[VegasNath]

Hi,

I have a date range G2:EB41953 - c5.3m cells, where the cell is blank or non numeric, I would like to populate with a zero. Is there a safe way to achieve this please?

[HansV]

I don't think it's a good idea to fill that many cells - Excel may bog down.

[VegasNath]

Yes, I fear that, but I'm happy to fill in smaller sections.

[Rudi]

You could try the following:

Select the range
Press F5 and then click the Special button
Select Blanks
Choose OK
With the blank cells selected, type a zero and then press CTRL+ENTER

[HansV]

And a variation on Rudi's suggestion to replace non-numeric values with 0:

Select the range
Press F5, then click Special...
Select Constants.
Clear the check box labeled Numbers.
Click OK.
Type a 0, then press Ctrl+Enter.

[VegasNath]

Great, Thanks both, worked a treat.

[VegasNath]

Is there by any chance a way to round all of the numbers in the range to 2 decimals?

[Rudi]

Is there by any chance a way to round all of the numbers in the range to 2 decimals?

Would formatting to 2 decimals suffice or do you actually want them rounded off.

[Rudi]

I tweaked a macro that I had on my PC to round off all the numerical values to two decimals using an array process.
This should run faster than calculating each cell on a sheet as the array is processed in memory.

Give it a try. (I did not test it thoroughly, so ensure you have a backup of your data).

Code: Select all

Sub RoundData()
'Get current state of various Excel settings
Dim screenUpdateState As Boolean, calcState As String, eventsState As Boolean, statusBarState As Boolean
    calcState = Application.Calculation
    eventsState = Application.EnableEvents
    statusBarState = Application.DisplayStatusBar

    Dim DataRange As Variant
    Dim rSource As Range, rDest As Range
    Dim R As Long
    Dim C As Long
    On Error GoTo EH:
    Set rSource = Application.InputBox("Select the entire range to copy.", Type:=8)
    Set rDest = Application.InputBox("Select first cell in range to paste.", Type:=8)

    'Turn off some Excel functionality to run code faster
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Application.EnableEvents = False
    Application.DisplayStatusBar = False

    DataRange = rSource   'Read all the values at once from the Excel grid, put into an array
    For R = 1 To UBound(DataRange, 1) 'First array dimension is rows.
        For C = 1 To UBound(DataRange, 2) 'Second array dimension is columns.
            DataRange(R, C) = Round(DataRange(R, C), 2) 'Round each element in array
        Next C
    Next R
    rDest.Resize(RowSize:=rSource.Rows.Count, ColumnSize:=rSource.Columns.Count).Value = DataRange    'Writes at once to destination
    DataRange = Null    'Reset range with filter

Reset:
    'After your code runs, restore state
    Application.DisplayStatusBar = statusBarState
    Application.EnableEvents = eventsState
    Application.Calculation = calcState
    Application.ScreenUpdating = True
    Exit Sub
EH:
    DataRange = Null
    Resume Reset
End Sub