Saturday, September 14, 2013

Insert Multiple Row before Unique Value through VBA



Region Product  Grand_Total
ANZ Tech                     159
ANZ OFM                       70
ANZ OFM                       70
TC HCIL                       41
TC HCIL                     297
ASEAN Apps                       80
ASEAN Apps                     587
ASEAN Systems                     350
ASEAN Apps                       34
MGI ORC                     600
MGI ORC                     658
MGI ORC                     750
DL ORG 340
DL ORG 123
DL ORG 107
GC Systems                     161
GC Apps                       83
GC Apps                       83
HR CI 611
HR CI 113
HR CI 596
IN Tech                     551
IN Tech                     832
IN Tech                       66
KR Tech                     275
KR OFM                       87
KR OFM                       81
MP HCL 665
MP HCL 579
HP MPGC 662
HP MPGC 672
HP MPGC 319
HP MPGC 772
HP MPGC 129


For example  if you want to insert a row after every unique region here is code


Option Explicit
Dim mycoll As Collection, strrow As String, finalstrrow As String
Dim myrng As Range, rowcount As Long, cell, i As Integer
Sub insertRowafterUnique()
rowcount = ThisWorkbook.Sheets(1).Range("A1").End(xlDown).Row
Set mycoll = New Collection
Set myrng = ThisWorkbook.Sheets(1).Range("A2:A" & rowcount)
strrow = vbNullString
On Error Resume Next
    For Each cell In myrng
        mycoll.Add cell, CStr(cell)

    Next
 
    For i = 2 To rowcount
            If ThisWorkbook.Sheets(1).Range("A" & i) <> ThisWorkbook.Sheets(1).Range("A" & (i + 1)) Then
                strrow = strrow & (i + 1) & ":" & (i + 1) & ","
             
 
 
            End If
    Next
    finalstrrow = Left(strrow, Len(strrow) - 1)
    ThisWorkbook.Sheets(1).Range(finalstrrow).EntireRow.Insert
End Sub

No comments:

Post a Comment