Delete 'n' number of characters from selected range

Sub Left_Delete()
        ' This module will delete text from Left in selected cells.
        Dim MyCell As Range
        Dim Total_Chars As Integer

        Total_Chars = InputBox("Please provide length of text to be deleted from left.", "Delete Text", 0)
        If IsNumeric(Total_Chars) Then
            For Each MyCell In Selection.Cells
                If Len(MyCell.Value) > Total_Chars Then
                    MyCell.Value = Mid(MyCell.Value, Total_Chars + 1, Len(MyCell.Value))
                End If
            Next
        Else
            MsgBox("Only numeric values are processed.")
        End If
        MyCell = Nothing
        On Error GoTo 0
    End Sub






    Sub Right_Delete()
        ' This module will delete text from Right in selected cells.
        Dim MyCell As Range
        Dim Total_Chars As Integer
        Total_Chars = InputBox("Please provide length of text to be deleted from right.", "Delete Text", 0)
        If IsNumeric(Total_Chars) Then
            For Each MyCell In Selection.Cells
                If Len(MyCell.Value) > Total_Chars Then
                    MyCell.Value = Mid(MyCell.Value, 1, Len(MyCell.Value) - Total_Chars)
                End If
            Next
        Else
            MsgBox("Only numeric values are processed.")
        End If
        MyCell =  Nothing
        On Error GoTo 0
    End Sub





    Sub Custom_Delete()
        ' This module will delete text from selected cells in custom manner.
        Dim MyCell As Range
        Dim Start_Pos As Integer
        Dim End_Pos As Integer
        Start_Pos = InputBox("Please provide start position of text to be deleted.", "Delete Text", 0)
        End_Pos = InputBox("Please provide end position of text to be deleted.", "Delete Text", 0)

        If Start_Pos < End_Pos Then
            For Each MyCell In Selection.Cells
                If Len(MyCell.Value) > End_Pos Then
                    MyCell.Value = Mid(MyCell.Value, 1, Start_Pos - 1) & Mid(MyCell.Value, End_Pos + 1, Len(MyCell.Value))
                End If
            Next
        Else
            MsgBox("Invalid Data Entered !")
        End If
        MyCell = Nothing
        On Error GoTo 0
    End Sub

0 Responses:

Post a Comment