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
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