Advertisement
Guest User

Untitled

a guest
Aug 4th, 2015
161
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 3.24 KB | None | 0 0
  1. Sub mukjizat2()
  2. Dim desc As String
  3. Dim sapnbr As Variant
  4. Dim shortDesc As String
  5.  
  6.  
  7. X = 1
  8. i = 2
  9.  
  10. desc = Worksheets("process").Cells(i, 3).Value
  11. sapnbr = Worksheets("process").Cells(i, 1).Value
  12. shortDesc = Worksheets("process").Cells(i, 2).Value
  13. Do While Worksheets("process").Cells(i, 1).Value <> ""
  14.  
  15. If desc = Worksheets("process").Cells(i + 1, 3).Value <> Worksheets("process").Cells(i, 3) Or Worksheets("process").Cells(i + 1, 2) <> Worksheets("process").Cells(i, 2) Then
  16. Delete.EntireRow
  17. Else
  18. Worksheets("output").celss(i + 1, 3).Value = desc
  19. Worksheets("output").Cells(i + 1, 1).Value = sapnbr
  20. Worksheets("output").Cells(i + 1, 2).Value = shortDesc
  21. X = X + 1
  22. End If
  23. i = i + 1
  24.  
  25. Loop
  26.  
  27.  
  28. End Sub
  29.  
  30. before :
  31.  
  32. sapnbr | ShortDesc | Desc
  33. 11 | black hat | black cowboy hat vintage
  34. 12 | sunglasses| black sunglasses
  35. 13 | Cowboy hat| black cowboy hat vintage
  36. 14 | helmet 46 | legendary helmet
  37. 15 | v mask | vandeta mask
  38. 16 | helmet 46 | valentino rossi' helmet replica
  39.  
  40. sapnbr | ShortDesc | Desc
  41. 11 | black hat | black cowboy hat vintage
  42. 13 | Cowboy hat| black cowboy hat vintage
  43. 14 | helmet 46 | legendary helmet
  44. 16 | helmet 46 | valentino rossi' helmet replica
  45.  
  46. Option Explicit
  47.  
  48. Sub mukjizat2()
  49. Dim ws As Worksheet
  50. Dim i As Long, lRow As Long
  51. Dim delRange As Range
  52.  
  53. '~~> This is your sheet
  54. Set ws = ThisWorkbook.Sheets("process")
  55.  
  56. With ws
  57. '~~> Get the last row which has data in Col A
  58. lRow = .Range("A" & .Rows.Count).End(xlUp).Row
  59.  
  60. '~~> Loop through the rows
  61. For i = 2 To lRow
  62. '~~> For for multiple occurances
  63. If .Cells(i, 2).Value <> "" And .Cells(i, 3).Value <> "" Then
  64. If Application.WorksheetFunction.CountIf(.Columns(2), .Cells(i, 2)) = 1 And _
  65. Application.WorksheetFunction.CountIf(.Columns(3), .Cells(i, 3)) = 1 Then
  66. '~~> Store thee row in a temp range
  67. If delRange Is Nothing Then
  68. Set delRange = .Rows(i)
  69. Else
  70. Set delRange = Union(delRange, .Rows(i))
  71. End If
  72. End If
  73. End If
  74. Next
  75. End With
  76.  
  77. '~~> Delete the range
  78. If Not delRange Is Nothing Then delRange.Delete
  79. End Sub
  80.  
  81. Sub hallelujah()
  82.  
  83. Dim duplicate(), i As Long
  84. Dim delrange As Range, cell As Long
  85. Dim delrange2 As Range
  86.  
  87. x = 2
  88.  
  89. Set delrange = Range("b1:b30000")
  90. Set delrange2 = Range("c1:c30000")
  91.  
  92. For cell = 1 To delrange.Cells.Count
  93. If Application.CountIf(delrange, delrange(cell)) > 1 Then
  94. ReDim Preserve duplicate(i)
  95. duplicate(i) = delrange(cell).Address
  96. i = i + 1
  97. End If
  98. Next
  99. For cell = 1 To delrange2.Cells.Count
  100. If Application.CountIf(delrange2, delrange2(cell)) > 1 Then
  101. ReDim Preserve duplicate(i)
  102. duplicate(i) = delrange(cell).Address
  103. i = i + 1
  104. End If
  105. Next
  106.  
  107. For i = UBound(duplicate) To LBound(duplicate) Step -1
  108. Range(duplicate(i)).EntireRow.Cut
  109. Sheets("output").Select
  110. Cells(x, 1).Select
  111. ActiveSheet.Paste
  112. Sheets("process").Select
  113. x = x + 1
  114. Next i
  115. end sub
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement