Excel Forum

Code: Select all

Sub Macro2()
    Dim r As Range
    For Each r In Selection
        r.Value = Replace(r.Value, ".", "")
    Next r
End Sub

Code: Select all

Public Sub test()
    Dim lRow As Long, rAll As Range, r As Range, iCount As Long, _
        sDocCode As String
    With Application
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
    End With
    With ActiveSheet
        iCount = 0
        lRow = .Range("a" & .Rows.Count).End(xlUp).Row
        Set rAll = .Range("a1:a" & lRow)
        For Each r In rAll
            If InStr(1, r.Value, "VW") Then
                sDocCode = Trim(Mid(r.Value, InStr(1, r.Value, "VW")))
            End If
            If IsNumeric(Trim(Mid(r.Value, 1, 5))) And Len(r.Value) = 118 Then
                .Cells(iCount + 1, "b") = sDocCode
                .Cells(iCount + 1, "c") = Trim(Mid(r.Value, 7, 13))
                .Cells(iCount + 1, "d") = Trim(Mid(r.Value, 21, 39))
                .Cells(iCount + 1, "e") = Trim(Mid(r.Value, 66, 8))
                iCount = iCount + 1
            End If
        Next r
        .Columns("a").Delete Shift:=xlLeft
        .Columns("a:d").AutoFit
        Set rAll = .Range("a1").CurrentRegion
        rAll.RemoveDuplicates Columns:=3, Header:=xlNo
    End With
    With Application
        .Calculation = xlCalculationAutomatic
        .ScreenUpdating = True
    End With
End Sub