Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Mar 2003
    Location
    London, Gtr London
    Posts
    131
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    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.
    before.JPGafter.JPG

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    2 Star Lounger
    Join Date
    Mar 2003
    Location
    London, Gtr London
    Posts
    131
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks a million HTH, that's worked a treat!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •