Remove numbers from dynamic range

[THEEND]

Hi

I am working on a solution to remove any stray numbers that may appear in text in a selected range: Range("N3:Q" & lastRow).Select.

I have found a partial solution on here by Rick Rothstein (see below) but it is only for one column and, with my rather limited VBA skills, I haven't been able to adapt it to work with my selection.

Any help would be greatly appreciated.

Many thanks

TE

Sub RemoveNumbersFromColumnB()
  Dim X As Long, R As Long, Data As Variant
  Data = Range("B1", Cells(Rows.Count, "B").End(xlUp))
  For R = 1 To UBound(Data)
    For X = 1 To Len(Data(R, 1))
      If Mid(Data(R, 1), X, 1) Like "[0-9]" Then Mid(Data(R, 1), X) = Chr(1)
    Next
    Data(R, 1) = Application.Trim(Replace(Data(R, 1), Chr(1), ""))
  Next
  Range("B1").Resize(UBound(Data)) = Data
End Sub

 

[Micron]

Which column would faithfully represent the last row with data? You're using B but looking over another range. If B has fewer rows than N, O, P, or Q those rows would not be included. Some things about that code could be changed I think. Why not just remove the number rather than change it to chr(1)? Gotta run for now though.

 

[THEEND]

Which column would faithfully represent the last row with data? You're using B but looking over another range. If B has fewer rows than N, O, P, or Q those rows would not be included. Some things about that code could be changed I think. Why not just remove the number rather than change it to chr(1)? Gotta run for now though.

Thanks Micron - yes, column B is best to determine the range as all cells will always be populated.

Regards,

TE

 

[Georgiboy]

Here is a way to do it with REGEX and REPLACE:

Sub RemoveNumbers()
    Dim rng As Range, var As Variant, x As Long, y As Long
    
    ' currently looking at range A:H, the end row is defined using column B
    Set rng = Range("A1:H" & Range("B" & Rows.Count).End(xlUp).Row)
    var = rng.Value

    With CreateObject("VBScript.RegExp")
        .Global = True
        .Pattern = "[0-9]"
        For x = 1 To UBound(var)
            For y = 1 To UBound(var, 2)
                var(x, y) = .Replace(var(x, y), "")
            Next y
        Next x
    End With
    rng = var
End Sub

 

[THEEND]

Thanks Georgiboy, that worked perfectly!

Very much appreciated!

Thanks again,

TE

 

[Georgiboy]

You're welcome, thanks for the feedback.