Wednesday, March 20, 2019

Replace "Tab Character" in Excel Active Sheet

TAB - horizontal tab Decimal Value is 9


Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim lRow As Long
Dim lCol As Long
   
    'Find the last non-blank cell in column A(1)
    lRow = Cells(Rows.Count, 1).End(xlUp).Row
   
    'Find the last non-blank cell in row 1
    lCol = Cells(1, Columns.Count).End(xlToLeft).Column

For i = 1 To lRow
    For j = 1 To lCol
        t = Cells(i, j).Value
        If (Len(t) > 0) Then
            If (Asc(Left(t, 1)) = 9) Then
                str1 = Mid(t, 2, Len(t))
                Cells(i, j).Value = str1
            End If
        End If
    Next
Next

End Sub

No comments:

Post a Comment

Your feedback is always appreciated. I will try to reply to your queries as soon as time allows.Please don't spam,spam comments will be deleted upon reviews.