Advertisement
Guest User

Untitled

a guest
Mar 28th, 2017
56
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 1.28 KB | None | 0 0
  1. Sub top10()
  2.  
  3. Dim r As Range, rC As Range
  4. Dim j As Long
  5.  
  6. 'Drinks top 10
  7. Worksheets("OLD_Master").Columns("A:H").Select
  8. Selection.sort Key1:=Range("H1"), Order1:=xlDescending, Header:=xlGuess, _
  9. OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
  10. DataOption1:=xlSortNormal
  11.  
  12. Worksheets("OLD_Master").Range("A:H").AutoFilter Field:=4, Criteria1:=Array( _
  13. "CMI*"), Operator:= _
  14. xlFilterValues
  15. Worksheets("OLD_Master").Range("A:H").AutoFilter Field:=5, Criteria1:="Drinks"
  16.  
  17. Set r = Nothing
  18. Set rC = Nothing
  19. j = 0
  20.  
  21. Set r = Range("B2", Range("B" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeVisible)
  22.  
  23. For Each rC In r
  24. j = j + 1
  25. If j = 10 Or j = r.Count Then Exit For
  26. Next rC
  27.  
  28. Range(r(1), rC).SpecialCells(xlCellTypeVisible).Copy
  29.  
  30. Worksheets("For Slides").Range("P29").PasteSpecial
  31. Worksheets("OLD_Master").ShowAllData
  32.  
  33. End Sub
  34.  
  35. Set r = Range("B2", Range("B" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeVisible)
  36.  
  37. Set r = Range("B2", Range("B" & Rows.Count).End(xlUp))
  38. ' Check if r is only 1 cell
  39. If r.Count = 1 Then
  40. r.Copy
  41. Else ' Your previous code
  42. Set r = r.SpecialCells(xlCellTypeVisible)
  43. For Each rC In r
  44. j = j + 1
  45. If j = 10 Or j = r.Count Then Exit For
  46. Next rC
  47. Range(r(1), rC).SpecialCells(xlCellTypeVisible).Copy
  48. End If
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement