Guest User

Untitled

a guest
Jan 18th, 2018
104
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 1.53 KB | None | 0 0
  1. Sub Two_Keep3Quarters()
  2. Dim Firstrow As Long
  3. Dim Lastrow As Long
  4. Dim lRow As Long
  5. Dim Tbl As ListObject
  6. Dim rng As Range
  7. Dim QuarterValue As Long
  8.  
  9. With Application
  10. .Calculation = xlCalculationManual
  11. .ScreenUpdating = False
  12. End With
  13.  
  14. With Sheets("Filtered Data")
  15. .DisplayPageBreaks = False
  16.  
  17. 'Set the first and last row to loop through
  18. Firstrow = 3
  19. Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
  20.  
  21. 'We loop from Lastrow to Firstrow (bottom to top)
  22. For lRow = Lastrow To Firstrow Step -1
  23. QuarterValue = .Range("G" & lRow).Value
  24.  
  25. 'We check the values in the Column G
  26. With .Cells(lRow, "G")
  27. If Not IsError(QuarterValue) Then
  28. If QuarterValue > 4 Then .EntireRow.Delete
  29. 'This will delete each row with value of more than 4 quarters
  30. End If
  31. End With
  32. Next lRow
  33. End With
  34.  
  35. Range("F1").Value = "Quarters"
  36. Range("G1").Value = "No. of Quarters"
  37.  
  38. On Error Resume Next
  39.  
  40. Set rng = Range(Range("A1"), Range("G1").End(xlDown)).SpecialCells(xlCellTypeBlanks)
  41. rng.Rows.Delete Shift:=xlShiftUp
  42.  
  43. For Each Tbl In Sheets("Filtered Data").ListObjects
  44. Tbl.Unlist
  45. Next
  46.  
  47. Set Tbl = ActiveSheet.ListObjects.Add(xlSrcRange, Range(Range("A1"), Range("G1").End(xlDown)), , xlYes)
  48. With Tbl
  49. .Name = "DataTable"
  50. .TableStyle = "TableStyleLight10"
  51. End With
  52.  
  53. Application.ScreenUpdating = True
  54. End Sub
Add Comment
Please, Sign In to add comment