How can I copy and paste data into empty columns faster?

Question

I am trying to have a code that copies and pastes data from the column prior if the cell is empty. I am targeting columns I, K, M, O, Q, and S.

This is what I am currently using:

Sub FillFutureRoles()
Dim iCell As Range
For Each iCell In Range("I:I,K:K,M:M,O:O,Q:Q,S:S")
   If iCell.Value = "" Then
   iCell.Value = iCell.Offset(0, -1).Value
   End If
Next iCell
End Sub

I am working with a data set of 600+ rows and growing, and when I tried to run this code, it was still running 30 minutes in. I know the code works as I have tried it with fewer columns and a smaller sample set, but it is not efficient for larger datasets.

For starters, you're looping over ~6.3 million cells, assuming your version of Excel has just over a million rows. One option is to [find the last used cell](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba). — BigBen, Jun 28 '21 at 17:18
There may be another option using `Range.SpecialCells(xlCellTypeBlanks)` too. — BigBen, Jun 28 '21 at 17:24
As always, do not loop through Excel cells, it's very slow. Instead use Range-Array copying. See here: https://stackoverflow.com/a/19167804/109122 or here: https://stackoverflow.com/a/68132284/109122 — RBarryYoung, Jun 28 '21 at 17:24
@RBarryYoung How would I implement the copy-paste with the range-array? I have never used a range-array before. Dim data() As Variant, and then data =? I am stuck here. — wmspaul, Jun 28 '21 at 17:39
Range-array isn't an object or type, it's just a term for the technique of copying range values to an array and/or copying array values to a range in bulk. — RBarryYoung, Jun 28 '21 at 18:55

score 2 · Accepted Answer · answered Jun 28 '21 at 19:41

2

Not able to test it right now, but I would probably do it like this:

Sub FillFutureRoles()
    ' get the last row
    Dim LastRow As Long, strRange As String
    LastRow = ActiveSheet.Cells.Find("*", LookIn:=xlFormulas, SearchOrder:=xlByRows, _
        SearchDirection:=xlPrevious).Row
    strRange = "H1:S" & CStr(LastRow)
    
    ' copy all values from the range to the array
    Dim vCells() As Variant, r As Long, c As Long
    vCells = Range(strRange).Value

    For r = 1 To LastRow
        For c = 2 To 12 Step 2
            If vCells(r, c) = "" Then
                vCells(r, c) = vCells(r, c - 1)
            End If
        Next c
    Next r
    
    ' copy all values from the array back to the range
    Range(strRange) = vCells
End Sub

answered Jun 28 '21 at 19:41

RBarryYoung

55,398
14
96
137

1

This worked beautifully! Thank you so much. – wmspaul Jun 29 '21 at 20:54
1

@wmspaul Great! How long does it take now? – RBarryYoung Jun 30 '21 at 12:14
1

it completes the task as soon as I click "Run," absolutely no waiting time at all. @RBarryYoung – wmspaul Jul 01 '21 at 13:03

Toddleson · Answer 2 · 2021-06-28T21:11:46.070

Whenever you write contents into ranges:

Excel recalculates the sheet and all formulas within it.
- This can be disabled with Application.Calculation = xlCalculationManual.
- After your code, reset it back to automatic with Application.Calculation = xlCalculationAutomatic
Excel refreshes the screen to display all new values.
- This can be disabled with Application.ScreenUpdating = False
- Don't forget to return it to true or the application will appear frozen.
If you have any, Excel will send a trigger to each Worksheet_Change or Workbook_Change script.
- You can disable those with Application.EnableEvents = False
- Again, don't forget to re-enable them afterwards.
Reading an writing to a Worksheet Object is slower than in Memory. Working with values in Arrays would be faster than working with Cells in a Range.
- VBA makes it easy to convert between ranges and arrays. You can do MyVariant = MyRange.Value which will then fill MyVariant with a 2 dimensional array of Variant values, each corresponding to the cell values in MyRange.
- After editing the array, you can put it back by doing MyRange.Value = MyVariant

When iterating over a large range, each individual cell edit will trigger a Worksheet calculation, a screen update and any _Change scripts. For illustrative purposes, if these take 1 ms to complete, a Sub that edits a million cells would take 17 minutes to execute.

score 0 · Answer 3 · answered Jun 29 '21 at 10:03

Please, try the next code:

Sub ReplaceBlancCells()
    Dim sh As Worksheet, lastRow As Long, rng As Range, cel As Range
    
    Set sh = ActiveSheet 'use here the necessary sheet
    'determine the last row:
    lastRow = sh.cells.Find("*", , xlFormulas, , xlByRows, xlPrevious).row
    'I, K, M, O, Q, and S.
    'Set a range of all blanc cells in the range to be processed:
    Set rng = Union(sh.Range("I1:I" & lastRow), sh.Range("K1:K" & lastRow), sh.Range("M1:M" & lastRow), _
                    sh.Range("O1:O" & lastRow), sh.Range("Q1:Q" & lastRow), sh.Range("S1:S" & lastRow)).SpecialCells(xlCellTypeBlanks)
   
    For Each cel In rng.cells 'iterate between blanc cells
        cel.value = cel.Offset(0, -1).value 'place the value from the left column
    Next
End Sub

How can I copy and paste data into empty columns faster?

3 Answers3