Excel VBA 自动添加新行并递增ID

用Excel 2010的VBA功能来处理数据，主要根据IP网段和网段IP个数，来生成每个IP地址，并进行ID编号。

Sub AutoInsert()    Line = 1531    Count = Cells(Line, 5).Value    ''Count = 5    For i = 2 To Count        ''Copy        Range(Cells(Line + i - 2, 1), Cells(Line + i - 2, 6)).Select        Selection.Copy        ''Insert        Range(Cells(Line + i - 1, 1), Cells(Line + i - 1, 6)).Rows.Insert        '' Change Id and Ip Cell Value        ''Id: ESP004001        IdVal = Cells(Line + i - 1, 2).Value        If i = 2 And InStr(IdVal, "ESP") = 0 Then            ''FH00318 ---> FH00318001             Cells(Line + i - 1, 2).Value = IdVal & "001"        Else            LastZeroPosInId = InStrRev(IdVal, "0")            IdPrefix = Left(IdVal, LastZeroPosInId)            If Len(IdVal) = LastZeroPosInId Then                IdValPart = 0            Else                IdValPart = Right(IdVal, Len(IdVal) - LastZeroPosInId)            End If            NewId = IdValPart + 1            ''Remove one zero, if NewId lengthen.            If Len(NewId) + Len(IdPrefix) > Len(IdVal) Then                IdPrefix = Left(IdPrefix, Len(IdPrefix) - 1)            End If            Cells(Line + i - 1, 2).Value = IdPrefix & NewId        End If        ''Ip: 202.123.176.63        IpVal = Cells(Line + i - 1, 4).Value        LastDotPos = InStrRev(IpVal, ".")        IpPrefix = Left(IpVal, LastDotPos)        IpValPart = Right(IpVal, Len(IpVal) - LastDotPos)        NewIp = IpValPart + 1        Cells(Line + i - 1, 4).Value = IpPrefix & NewIp    Next i    ''ActiveWorkbook.SaveEnd Sub

处理前：

处理后：

这样可以从繁重的重复劳动中解放出来。