Find and Replace non numeric values in a range of cells with a 0

Topic

New Reply

Hello Experts

I’m trying to find non numeric values in a range of cells in a column and replace them with a 0.

Column O will always hold the values I need and there will always be a heading in cell “O1” but the number of cells/rows down in the column I’m searching will vary – If I “hard-code” the Cell range e.g. “O2:O14” then I can see the code running down the column and replacing the non numeric values.

This is the code I have so far, I’ve found various snippets of code on the net and tried to piece them together here but as you can probably tell I cannot quite define the Range dynamically depending on the number of rows with data in, could anyone help please?

I hope I’m making sense

Thanks
Hayden

Code:

Sub FindNonNumerics()
    
    Dim c As Range, rng
    ‘ ActiveSheet.Range(“O2”, ActiveSheet.Range(“O2”).End(xlDown)).Select ‘– 1st attempt
    Set rng = Range(“O2”, Range(“O2”).End(xlDown)).Select
    For Each c In rng
        If Not IsNumeric(c.Value) Then
            c.Value = 0
        End If
    Next c
    
End Sub

Reply | Quote

Replies

Hayden,

Only one minor change needed the rest is cosmetic/personal preference!

Code:

Sub FindNonNumerics()
    
    Dim rngCell  As Range
    Dim rng      As Range

    Set rng = Range("O2", Range("$O$2").End(xlDown))
    
    For Each rngCell In rng
        If Not IsNumeric(rngCell.Value) Then
            rngCell.Value = 0
        End If
    Next rngCell
    
End Sub

I just dropped the .select from the definition of rng!
It worked fine on my test data.
37337-before37338-after

HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Thanks a million HTH, that’s worked a treat!!

Reply | Quote