I have two worksheets with ranges, one is 452 rows by 11 columns and the other is 152 rows by 28 columns. The ranges are not only filled with data but make heavy usage of conditional formatting.

Each day, I need to shift all of the rows down one row. Doing a copy/paste of the entire range less the first row blows up on memory usage.

I wrote the following VBA routine to handle the shift one row at a time ... this one handles the 452x11. It does the job but takes 20 seconds on the 452x11 range and same routine takes 40 seconds on the 152x28 range.

  Dim iRangeRowX As Integer
  Dim iRangeRowsInRange As Integer
  Dim iRangeBeginsRow As Integer
  Dim iRangeEndsRow As Integer
  Dim rRange As Range
  
  Application.ScreenUpdating = False
  Application.Calculation = xlCalculationManual
  Worksheets("BreadthModels").Activate
  Set rRange = Worksheets("BreadthModels").Range("BreadthModelTable")
  With rRange
    iRangeRowsInRange = .Rows.Count  ' row count for this range
    iRangeBeginsRow = .Row     ' first row for this range
    iRangeEndsRow = (iRangeBeginsRow + iRangeRowsInRange) - 1
    ' columns are hard coded
    For iRangeRowX = (iRangeBeginsRow + 2) To (iRangeEndsRow - 1) Step 1
      Range("E" & iRangeRowX & ":P" & iRangeRowX).Select
      Application.CutCopyMode = False
      Selection.Copy
      Range("E" & iRangeRowX - 1 & ":P" & iRangeRowX - 1).Select
      ActiveSheet.Paste
      DoEvents  'allow break
    Next
  End With
  Application.Calculation = xlCalculationAutomatic
  Application.ScreenUpdating = True
  Calculate
Is there anything which can be done to speed execution? Thank you.

Earl