How can i apply remove alphabet function on active sheet loop?

Question

Function StripChar(Txt As String) As String
With CreateObject("VBScript.RegExp")
.Global = True
.Pattern = "\D"
StripChar = .Replace(Txt, "")
End With
End Function

So i am trying to apply this function on bottom range via loop through cells

Sub Alphabetremove()
    Dim ws As Worksheet
    Dim Lastrow As Integer
    Set ws = ActiveSheet
    Lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
    Range("F2:F" & Lastrow).Select
    With Selection
    .Value = StripChar(.Value)
End With
End Sub

you will need to loop and run each value through the function one at a time. — Scott Craner, Oct 29 '19 at 21:01
You might benefit from reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). — Pᴇʜ, Oct 30 '19 at 11:05

score 1 · Accepted Answer · answered Oct 30 '19 at 11:10

The issue is that you cannot run the function .Value = StripChar(.Value) on a range at once (as you tried) but only on a single cell. Therfore you need to loop from row 2 to LastRow and apply the function to each single cell.

Also note that row counting variables need to be of type Long because Excel has more rows than Integer can handle. Actually I recommend always to use Long instead of Integer as there is no benefit in using Integer in VBA.

Also if you set your worksheet to a variable ws you need to use this variable for all .Cells and .Range objects otherwise this is useless.

Option Explicit

Public Sub Alphabetremove()
    Dim ws As Worksheet
    Set ws = ActiveSheet 'better define a workseet by name unless you use the code for multiple worksheets: 
                         'Set ws = ThisWorkbook.Worksheets("MySheet")

    Dim LastRow As Long
    LastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

    Dim Cell As Range
    For Each Cell In ws.Range("F2:F" & LastRow)
        Cell.Value = StripChar(Cell.Value)
    Next Cell
End Sub

Finally I highly recommend you to read How to avoid using Select in Excel VBA and apply this to all of your code.

How can i apply remove alphabet function on active sheet loop?

1 Answers1