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
Bookmarks