Apply the UDF to the used range

[Tim Hanson]

I am trying to apply the UDF to the used range to remove any leading "-" but I am not getting how to write the Sub so as to input the "-"

Thanks for any help

Sub RemoveFirstCharIF()
    Dim myArray As Variant
    Dim ws As Worksheet
    Dim LastRow As Long
    Dim LastCol As Long
    Dim x As Long
    Dim y As Long
   
       
    Set ws = ThisWorkbook.Sheets("AAPIP_Data_Prepped")
            With ws
                LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
                LastRow = .Cells(.Rows.Count, LastCol).End(xlUp).Row
            End With
               

            With ws
                    myArray = .Range(.Cells(1, 1), .Cells(LastRow, LastCol))
            End With

            For x = LBound(myArray) To UBound(myArray)
                For y = LBound(myArray, 2) To UBound(myArray, 2)
                    myArray(x, y) = RFC(myArray(x, y))
                Next y
            Next x

            ws.Range(Cells(1, 1), Cells(LastRow, LastCol)) = myArray
           
End Sub


'\\ Remove 1st Char If... and change to something else...
'RFC
Public Function RFC(ByVal RemFstChar As String) As String

  If Left(RemFstChar, 1) = "1" Then
      RemFstChar = Replace(RemFstChar, "1", "")
  End If

  RFC = RemFstChar
End Function

 

[NARAYANK991]

Hi Tim ,

If you wish to remove only hyphens which are the first character of cells , then modify the function RFC as follows :

Public Function RFC(ByVal RemFstChar As String, CharToBeRemoved As String) As String
  If Left(RemFstChar, 1) = CharToBeRemoved Then
      RemFstChar = Replace(RemFstChar, CharToBeRemoved, "", , 1, vbTextCompare)
  End If

  RFC = RemFstChar
End Function

It is better to make the function more general by passing the character to be removed as a parameter , so that in case you want some other character to be removed , the same function can be reused.

The 1 in the 5th parameter position ensures that only the left-most character will be removed , in case the same character occurs elsewhere within the RemFstChar string.

Narayan

 

[Tim Hanson]

Yes Narayan, that was exactly what I needed, then I changed

This (from the Sub)

myArray(x, y) = RFC(myArray(x, y))

To this

myArray(x, y) = RFC(myArray(x, y), "_")

thank you